Excel 2007 transpose/combine multiple rows into one
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?
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
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.