Moving data from multiple rows into a single row in Excel

Hi All.

I have been searching on the web for an answer to my question and have come across similar questions, but unfortunately, haven't yet seen an answer for what I am after. I am not familiar with Pivot Tables, Macros or VBA/VBE. I was hoping someone here might be able to help.

It's for an Excel file which contains thousands of rows of records. The problem is that some columns use up more than one row and I would like to know how the data in those rows could be combined into just one row. I use the term 'combine' here, but I have seen all kinds of other words used on the web, such as consolidate, transpose and concatenate, to name a few!

Here is an example of the current setup (sorry about some of the names not aligning correctly).


           Column 1    Column 2
           Member No.    Users

Row 1    ABC001       Tony Walters Jack Holder Mary White
Row 2    ABC002       John Fellman James Burton
Row 3    ABC003       Bob Brown
Row 4                          Susan Stone
Row 5                            Harry Day
Row 6                            Betty Smith
Row 7    ABC004       Jane Benning
Row 8    ABC005       Howard Hill
Row 9    ABC006       Jake Riley
Row 10                            Simon Pegg
Row 11                            Janet Peters
Row 12                        Joan Gill
Row 13    ABC007       Paul Dalton

I want to have the data that uses more than one row in the example above, to use just one row like in the examples below.

Example 1-

Row 1    ABC001    Tony Walters Jack Holder Mary White
Row 2    ABC002    John Fellman James Burton
Row 3    ABC003    Bob Brown Susan Stone Harry Day Betty Smith
Row 4    ABC004    Jane Benning
Row 5    ABC005    Howard Hill
Row 6    ABC006    Jake Riley Simon Pegg Janet Peters Joan Gill
Row 7    ABC007    Paul Dalton
Row 8
Row 9
Row 10
Row 11
Row 12
Row 13
   

OR EXAMPLE 2-

Row 1    ABC001    Tony Walters Jack Holder Mary White
Row 2    ABC002    John Fellman James Burton
Row 3    ABC003    Bob Brown Susan Stone Harry Day Betty Smith
Row 4
Row 5
Row 6
Row 7    ABC004    Jane Benning
Row 8    ABC005    Howard Hill
Row 9    ABC006    Jake Riley Simon Pegg Janet Peters Joan Gill
Row 10
Row 11
Row 12
Row 13    ABC007    Paul Dalton

OR EXAMPLE 3-

Row 1    ABC001    Tony Walters Jack Holder Mary White
Row 2    ABC002    John Fellman James Burton
Row 3    ABC003    Bob Brown
                                Susan Stone
                                Harry Day
                                Betty Smith
Row 4    ABC004    Jane Benning
Row 5    ABC005    Howard Hill
Row 6    ABC006    Jake Riley
                                Simon Pegg
                                Janet Peters
                                Joan Gill
Row 7    ABC007    Paul Dalton

OR EXAMPLE 4-

Row 1    ABC001    Tony Walters Jack Holder Mary White
Row 2    ABC002    John Fellman James Burton
Row 3    ABC003    Bob Brown
                                Susan Stone
                                Harry Day
                                Betty Smith
Row 4   
Row 5
Row 6
Row 7    ABC004    Jane Benning
Row 8    ABC005    Howard Hill
Row 9    ABC006    Jake Riley
                                Simon Pegg
                                Janet Peters
                                Joan Gill
Row 10   
Row 11   
Row 12
Row 13    ABC007    Paul Dalton


In examples 2 and 4, would there be an easy way to delete all the blank rows?

In the actual spreadsheet, there will be more than one column which contains data that uses more than one row, but for now if I can get one column to do the trick, then I can always do the other columns individually.

The solution must allow for the sorting/manipulation of data within the spreadsheet.

If there is anyone who may know of a way to achieve the desired results, it would be really appreciated if you could help.

Thanks.

Hi All.

As I have posted this question on another forum as well and not received any replies there either, I am assuming that it is not possible to do as I ask.

I am also assuming that the difficulty lies in the fact that Column 1 contains blank cells and so, there is no way that Excel can distinguish which rows in Column 2 need to go with which rows in Column 1. The examples/solutions that I have come across on the web all have text/data in the first column, for example, there would be a number of ABC001 entries on various rows and then these can all be moved to just one ABC001 row.

Thanks to anyone who gave my question any thought and wasn't able to answer it.

I am very good with Excel, having spent a few years making a living with it as well as Access. I looked hard at your needs, but I can think of no way of achieving what you want.

Excel is primarily a data cruncher, with limits on sorting. That is an area where databases shine.

Thanks for the replies and effort. As always, very much appreciated!

Ja, at the moment, I don't normally come across anyone who would know this kind of thing. I could try contacting one or two people who I think may know a little about scripts and things but I doubt they'd know this. My usual thinking is that Microsoft know how to use the features provided in their program but may not know how to do code/scripts for other things which the program can't do by itself. Of course I may be totally wrong! I've just checked and yes, there seems to be an Excel forum; thanks, it's worth a try! I think there are charges for contacting support.

Scuzzy, can Excel be made to fill the blank rows beneath a cell with the same content as the cell? So in the first setup shown in my original post, if ABC003 can be automatically copied to rows 4, 5 and 6 and ABC006 copied to rows 10, 11 and 12, then there wouldn't be any blank cells and maybe a script could be used to move all the ABC003 data to one row and same with ABC006, etc.

Thanks.

Trav, I don't know of a way of doing that, or even if it's possible.

Have you tried sorting the data and then dragging the cells down to auto-populate the empty cells?

Hi Scuzzy.

Thanks for the reply.

The reason for my question IS so that it would allow me to sort! Haha. It can't be sorted because then the 'Member No.' wouldn't align correctly with the 'Users' since the 'Users' use more than one row. In any case, remember it's thousands of records, so manually dragging each one down would be too time consuming.

Will poast back if I learn anything new in the meantime.

Hmm. I see that now.

I have taught Microsoft Office at a local college and don't know of a way to do what you want to achieve. In spreadsheets, it is very important to plan your layout carefully to avoid these kind of problems.

I am sorry that no one was able to help you, but there may be a time that someone with a trick will see this thread in the future or Microsoft will add more functionality into a future version.

Mark H

Hi Mark H.

Thanks for taking the time to reply. The data was not entered directly into Excel; it is entered on another system and can then be exported to an Excel file. I don't know why anyone would set up their system so that the exported data takes such an inconvenient layout.  I don't know how their system has been created and don't have a clue if this would be at all possible, but I'll ask them at the next opportunity if their system could be set so that exported data does not utilise multiple rows for any field.