Transpose row to column data

siddhik · Jun 15th 2016

Hi,

Attachment Sheet1 has the raw data whereas the Result sheet has the excepted data.
I have to carry this activity manually for 1000's records, would like to do it with VBA code.

skywriter · Jun 15th 2016

Re: Transpose row to column data

Here's the code and a workbook below that. :cool:

Code

Sub siddhik()
Dim dict As Object, a, j, k, c As Long
Set dict = CreateObject("Scripting.Dictionary")
Application.ScreenUpdating = False
Worksheets("Results").UsedRange.ClearContents
a = Worksheets("Sheet1").Range("A1").CurrentRegion
For c = LBound(a, 1) To UBound(a, 1)
            With dict
                If Not (a(c, 1) = Empty Or .Exists(a(c, 1))) Then
                    .Add a(c, 1), a(c, 1) & "," & a(c, 2)
                    Else
                    .Item(a(c, 1)) = .Item(a(c, 1)) & "," & a(c, 2)
                End If
            End With
Next c
c = 1
    For Each k In dict.items
        j = Split(k, ",")
        Worksheets("Results").Cells(c, 1).Resize(1, UBound(j) + 1).Value = j
        c = c + 1
    Next k
Application.ScreenUpdating = True
End Sub

Display More

Transpose row to column data

Participate now!

Share