Excel 2010 Move data from multiple columns/rows to single row

So frustrating! I get data sent to me and it looks like this:

a  1
a     2  2
a           3  3
b  1
b     2  2
b           3  3
b                 4  4
b                       5  5
b                             6  6

and I need it to look like this:

a  1  2  2  3  3
b  1  2  2  3  3  4  4  5  5  6  6

I have about 30 columns that need to move to the top value in their group, then removing the duplicates (to which there are about 33 rows of duplicates, trying to get it down to about 8 rows). I have been searching forums for several days and trying bits and pieces of code. I am having such a tough time with VBA!!!!

Same illustration, but graphically:

    →    

Solution 1:

You can achieve this without VBA in a few seconds. Just follow these steps.

WARNING: This process will overwrite your original data. If you want to keep the original data, do not do this.

1. Select your data, as far to the right as you need to select everything.

   

2. Press F5. Click Special... in the Go To dialog that pops up. Select Blanks and click OK. This will select all the blank cells in your data.

   

3. Without clicking anywhere, paste the following into the formula bar. Be sure to adjust the cell references to match your data.

   =IF($A2=$A1,C2,"")

   Press Ctrl+Enter. This will apply the formula to all the selected cells. The formula checks if the row below has the same value in column A. If so, it returns the value immediately below. If not, it returns nothing.

   

4. Now, select all your data again, copy it and Paste Special... > Values back into the same range. This will remove the formulas you just entered but will keep the values.

5. Finally, with all your data selected, go to the Data ribbon and select Remove Duplicates. Remove duplicates based on column A (the column with values a and b).

   

   This will keep the top row for each value in column A and remove the rest. You should be left with what you want.