How to transpose multiple cells in row to column in Excel

Question

I have data that looks like:

name    field1  field2  field3
John    abc     dcf     fdd
Sue     ddf     
Beth    fds     rtfds

And I want it to look like this:

abc     John
dcf     John
fdd     John
ddf     Sue
fds     Beth
rtfds   Beth

Note the rows have variable numbers of fieldx columns... I'm trying to use a VBA script from MrExcel and getting an "application defined or object defined error."

Is there a way to do this with just an excel formula? I can post the VBA script, but if there is a formula solution that would be better.

What you are looking for is called `unpivot`. You can either [do it with VBA](http://stackoverflow.com/questions/33790370/efficiently-reformat-data-layout/33790599#33790599) or [you can do it manually](http://stackoverflow.com/questions/20541905/convert-matrix-to-3-column-table-reverse-pivot-unpivot-flatten-normal/20543651#20543651). I am unaware of any solution using formulas only. — Ralph, Oct 11 '16 at 15:48
see here: http://stackoverflow.com/questions/20541905/convert-matrix-to-3-column-table-reverse-pivot-unpivot-flatten-normal — Scott Craner, Oct 11 '16 at 15:48

score 0 · Answer 1 · answered Oct 11 '16 at 15:48

Assuming your data starts in Cell A1 of Sheet1 and you want to transpose to Sheet2, something like the following would work:

Sub test()

    Dim s1 As Worksheet
    Dim s2 As Worksheet
    Dim lCol As Long
    Dim i As Long
    Dim lRow As Long

    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")

    With s1

        lRow = .Cells(.Rows.Count, 1).End(xlUp).Row

        For i = 2 To lRow

            lCol = .Cells(i, .Columns.Count).End(xlToLeft).Column

            .Range(.Cells(i, 2), .Cells(i, lCol)).Copy

            s2.Cells(1, i - 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
                False, Transpose:=True

        Next i

    End With

End Sub

This does not produce the outcome the OP desires. – Scott Craner Oct 11 '16 at 15:55 — Scott Craner, Oct 11 '16 at 15:55

How to transpose multiple cells in row to column in Excel

1 Answers1