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.
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.
Re: Transpose row to column data
Here's the code and a workbook below that. :cool:
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
Don’t have an account yet? Register yourself now and be a part of our community!