Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

2 Answers 2

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)
        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
share|improve this answer
    
Worked great on the 21,000 row spreadsheet! –  jzd Feb 11 '11 at 22:16
add comment

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.

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.