Transpose the column values into row values

Question

I would like to use excel 2010 VBA to realize a function to transpose the value from column to row, and if there are more than 10 values in the same row, the 11th value return to the second row.

For example:

=========

ColA  ColB
1111  AAA
1111  BBB
1111  CCC
1111  DDD
1111  EEE
1111  FFF
1111  GGG
1111  HHH
1111  III
1111  JJJ
1111  KKK
1111  LLL
2222  MMM
2222  OOO
2222  PPP

Desired:

ColA   Val1   Val2   Val3    Val4   Val5   Val6   Val7    Val8    Val9    Val10 
1111   AAA    BBB    CCC    DDD     EEE    FFF    GGG     HHHH    III     JJJ
1111   KKK    LLL  
2222   MMM    OOO    PPP

=========

I've tried to first group the value into one field with delimiter "," and then I use the excel function to separate the data into different columns. here is the code to group the values but i dont know how to tell excel to go the second row if there are more than 10 values.

here is the code to group the values:

 Sub combineValues()
    Dim dic As Dictionary
     Dim key, val, i, p, k
    Set dic = New Dictionary
    For i = 1 To Worksheets(1).Range("A65536").End(xlUp).Row
        key = Worksheets(1).Cells(i, 1).Value
        val = Worksheets(1).Cells(i, 2).Value
        If dic.Exists(key) Then
            dic.Item(key) = dic.Item(key) & ", " & val
        Else
            dic.Add key, val
        End If
    Next
    p = 1
    For Each k In dic.Keys
        Worksheets(2).Cells(p, 1) = k
        Worksheets(2).Cells(p, 2) = dic.Item(k)
        p = p + 1
    Next
End Sub

with the code, I can group the value into one row, like this:

ColA  ColB
1111  AAA,BBB,CCC,DDD,EEE,FFF,GGG,HHH,III,JJJ,KKK,LLL
2222  MMM,OOO,PPP

and then I use excel function to separate all these value into different fields, mainly like this:

ColA   Val1   Val2   Val3   Val4   Val5   Val6   Val7   Val8   Val9    Val10 Val11 Val12 
1111   AAA    BBB    CCC    DDD     EEE    FFF    GGG    HHH    III     JJJ  KKK    LLL  
2222   MMM    OOO    PPP

but the problem is I don't want more than 10 values appear in the same row, I want to tell if there are more than 10 values, it return to the second row for the rest value.

@Kyle: I've updated with my progress so far. Please help! thank you very much in advance. — Shania
– Shania, Commented Mar 20, 2015 at 16:30

Amnney · Accepted Answer · 2015-03-20 19:01:24Z

1

Okay so here's my second try on this:

Sub test2()
    Dim search As Long
    Dim j As Long
    Dim l As Long
    Dim cCount As Long
    Dim aCount As Long

    aCount = 1

    For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        l = 2
        j = i
        cCount = 0

        search = Cells(i, 1).Value
        Worksheets("test").Cells(aCount, 1).Value = Cells(i, 1).Value
        While search = Cells(j, 1).Value
            If l = 12 Then
                aCount = aCount + 1
                Worksheets("test").Cells(aCount, 1).Value = Cells(i, 1).Value
                l = 2
            Else
             Worksheets("test").Cells(aCount, l).Value = Cells(j, 2).Value
                j = j + 1
                l = l + 1
                cCount = cCount + 1
            End If
        Wend
        aCount = aCount + 1
        i = i + cCount - 1
    Next i
End Sub

This time you enter this:

enter image description here

and get this:

enter image description here

This time it checks for the value of ColA and as long as the value of ColA is the same it will put the value of ColB in the next column to the right with a break every 10th column.

best regards Amnney

Maybe my english isn't good enough to understand the issus but isn't that the result you desired?

Before:

enter image description here

After:

enter image description here

edited Mar 20, 2015 at 19:01

answered Mar 20, 2015 at 16:38

Amnney

484 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Amnney Over a year ago

I changed the code a little bit since it didn't calculate the column properly.

Shania Over a year ago

I've test your code, it transpose the column to the rows but it includes the colA value too. so each time, it repeat the same value of ColA, it needs to first group the value, if it's in the same colA, and then transpose the value of colB into rows, and if it's more than 10 values, return the value into the second row like the above desired result.

Shania Over a year ago

thank you very much for the reply but it's not what I want, maybe i dont explain well. In fact, in my raw data, there are two columns, colA is for ID, colB is for value as shown at the beginning of the example on the top. If the ID(ColA) is the same, I want to transpose the value (ColB) from column to row, and the row only contains for 10 values. If there are more than 10 values, it returns to the second row. The value of ColA(ID) doesnt transpose. The desired result is above. So if i understand well, your code provided is just to transpose all the columns values to the rows.

Shania Over a year ago

Thank you so much. That's exactly what I want!

Amnney Over a year ago

I'm realy happy that I could help you out :)

Collectives™ on Stack Overflow

Transpose the column values into row values

Desired:

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Desired:

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related