Excel VBA code to represent data from row wise to column wise

Question

Hi,

I am having data in two columns as below

column1 column2

top level sub level

a a1

a a2

a a3

a a4

b b1

b b2

b b3

b b4

b b5

now i need the data to get represented column wise as below. Since the data is huge VBA code would be appropriate.

column1 column2 column3 column4 column5 coumn6

like

a a1 a2 a3 a4

b b1 b2 b3 b4 b5

Regards,

shiv

Answer 1

Try this on a copy of your sheet - it was not clear if you have headings or not.... so I assumed "not"

Sub TestMacro()

    Dim cUnique As Collection

    Dim Rng As Range

    Dim Cell As Range

    Dim lRow As Long

    Dim vNum As Variant

    Dim i As Integer

    Dim sh As Worksheet

    Set sh = ActiveSheet

    'Extract unique entries from column A

    Set Rng = sh.Range("A1", sh.Cells(sh.Rows.Count, "A").End(xlUp))

    Set cUnique = New Collection

    On Error Resume Next

    For Each Cell In Rng.Cells

        cUnique.Add Cell.Value, CStr(Cell.Value)

    Next Cell

    On Error GoTo 0

    'Write out the unique values to C

    i = 1

    For Each vNum In cUnique

        sh.Cells(i, "C").Value = vNum

        i = i + 1

    Next vNum

    'Write out the other values to the columns

    For Each Cell In Rng

        lRow = Application.Match(Cell, sh.Range("C:C"), False)

        sh.Cells(lRow, sh.Columns.Count).End(xlToLeft)(1, 2).Value = Cell(1, 2).Value

    Next Cell

    'Delete the original

    sh.Range("A:B").Delete

End Sub