Transposition of multiple rows

The worksheet contains column A that lists a name (A2) and under that name, 11 cells containing labels. Column B lists corresponding data on the same row, for each label in A.
In the original worksheet, this is repeated a few hundred times.
In the example, the 11 cells have been manually transposed to the same row as the "Name" label, starting from Column E, and extending to Column O.
And the data from column B has been manually transposed across these columns, on to the same row as the name.

Required: a script or formula to do this for several hundred entries.
Who is Participating?
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may try something like this....
In the attached, click the button called "Transform Data" on Sheet2 to run the code.
Sub TransformData()
Dim sws As Worksheet, dws As Worksheet
Dim slr As Long, dlr As Long, i As Long

Application.ScreenUpdating = False

Set sws = Sheets("Sheet2")
slr = sws.Cells(Rows.Count, 2).End(xlUp).Row

On Error Resume Next
Set dws = Sheets("Output")
On Error GoTo 0

If dws Is Nothing Then
    Set dws = Sheets.Add(after:=sws)
    dws.Name = "Output"
End If
dws.Range("B1").Value = "Name"
dws.Range("C1").PasteSpecial xlPasteAll, Transpose:=True

For i = 2 To slr Step 12
    dlr = dws.Range("B" & Rows.Count).End(3)(2).Row
    sws.Cells(i, 2).Copy dws.Range("B" & dlr)
    sws.Cells(i + 1, 2).Offset(0, 1).Resize(11).Copy
    dws.Range("C" & dlr).PasteSpecial xlPasteAll, Transpose:=True
Next i
dws.UsedRange.WrapText = False
dws.Range("B1").CurrentRegion.Borders.Color = vbBlack
Application.ScreenUpdating = True
End Sub

Open in new window

gregfthompsonAuthor Commented:
Thank you very much.
gregfthompsonAuthor Commented:
Thanks heaps.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.