Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
I don't have an answer, but it might matter, so: is the data sorted on the first column? Specifically, do you know if every instance of 1001NCCN (and the like) will always be adjacent to one another, or is it possible to have them split? – DMA57361 Feb 11 '11 at 14:20
@DMA, Yes every instance is adjacent to one another. They are not sorted alphabetically but I can do that if needed. – jzd Feb 11 '11 at 14:37
up vote 5 down vote accepted

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)

        ' 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

        ' next row
        Set rw = rw.Offset(1, 0)
End Sub
share|improve this answer
Worked great on the 21,000 row spreadsheet! – jzd Feb 11 '11 at 22:16

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:


and at G1 write:


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

share|improve this answer
Interesting approach. I will look into this the next time I find myself in this situation. – jzd Feb 14 '11 at 13:41

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .