Excel 2007 transpose/combine multiple rows into one

Question

I have data like so:

1001NCCN    3618127
1001NCCN    208478
1001NCCN    207316
1001TEMN    409889
1001TEMN    801651
1001TEMN    273134
1001TEMN    208478
1001TEMN    207316

I need to transpose/combine the rows that have matching values in the first column with a final result like so:

1001NCCN    3618127 208478  207316      
1001TEMN    409889  801651  273134  208478  207316

I looked at Pivot Tables, and filtering but neither seemed like they can give me what I need. Is there a way to do this within Excel?

Answer 1

Here's a VBA solution

it relies on like rows being grouped, so sort first if you need to

Just select the first cell in your range and run...

Sub Mergeitems()
    Dim cl As Range
    Dim rw As Range

    Set rw = ActiveCell

    Do While rw <> ""
        ' for each row in data set
        '   find first empty cell on row
        Set cl = rw.Offset(0, 1)
        Do While cl <> ""
            Set cl = cl.Offset(0, 1)
        Loop

        ' if next row needs to be processed...
        Do While rw = rw.Offset(1, 0)
            cl = rw.Offset(1, 1)       ' move the data
            Set cl = cl.Offset(0, 1)   ' update pointer to next blank cell
            rw.Offset(1, 0).EntireRow.Delete xlShiftUp   ' delete old data
        Loop

        ' next row
        Set rw = rw.Offset(1, 0)
    Loop
End Sub

Answer 2

IMO the simplest way to perform such a task is by means of Google docs. Copy your data in an empty Google spreadsheet. Then at, say, F1 write:

=UNIQUE(A:A) 

and at G1 write:

=TRANSPOSE(FILTER(B:B,A:A=F1)

and copy down. Then copy results and paste them back into Excel.