How to transpose a column based on similar values

**tdunford7** · 02-27-2012, 11:33 PM

I am trying to figure out how to transpose 1 column a data that I have that has groups of the same number in column A, such as:

Column A Column B END RESULT in Column C
2596 11 11,22,33
2596 22
2596 33
2794 123 123,456,789,1011,1213
2794 456
2794 789
2794 1011
2794 1213

Column C above is what I'm trying to achieve. Also, the rows do not really have to have a comma between each value, so if they have to be placed in separate cells down the row, then that will be fine. Any help with this would be greatly appreciated. Thanks.

**DGagnon** · 02-27-2012, 11:35 PM

check out this post from earlier today:

http://www.excelforum.com/excel-2007...of-number.html

**tdunford7** · 02-27-2012, 11:50 PM

That's not what I'm after. I've created a sample worksheet so you can see exactly so I don't have to try to explain it.

**tdunford7** · 02-28-2012, 12:40 AM

I've found the answer doing a little more digging here: http://www.excelforum.com/excel-prog...o-columns.html

Thanks, StephenR!!!

**nmhung49** · 02-28-2012, 12:51 AM

Originally Posted by tdunford7

I've found the answer doing a little more digging here: http://www.excelforum.com/excel-prog...o-columns.html

Thanks, StephenR!!!

OhOh
I'm writting code for you
Try

PHP Code:


Sub test() 
Dim sArray, RArray, iRow As Long, i As Long 
Dim Dic As Object 
Set Dic = CreateObject("Scripting.Dictionary") 
With Sheet1.Range("A1").CurrentRegion 
    sArray = .Value 
 
ReDim RArray(1 To UBound(sArray, 1), 1 To 255) 
For iRow = 1 To UBound(sArray, 1) 
    If Not Dic.exists(sArray(iRow, 1)) Then 
        i = i + 1: iRow = iRow 
        Dic.Add sArray(iRow, 1), iRow 
        RArray(iRow, 1) = sArray(iRow, 2) 
        RArray(iRow, 255) = 1 
    Else 
        RArray(Dic.Item(sArray(iRow, 1)), 255) = RArray(Dic.Item(sArray(iRow, 1)), 255) + 1 
        RArray(Dic.Item(sArray(iRow, 1)), RArray(Dic.Item(sArray(iRow, 1)), 255)) = sArray(iRow, 2) 
    End If 
Next iRow 
 
    .Offset(, 2).Resize(UBound(sArray), 254).Value = RArray 
End With 
End Sub