Unpivoting an Excel table with 100 columns into rows

Question

I have a table in my Excel sheet that looks like this:

Code  _01001  _01002
----------------------
1         88     371
2         88     371
3         88     371

And I want to convert it to the following format:

Code    Column  Value
----------------------
1       _01001     88
1       _01002    371
2       _01001     88
2       _01002    371
3       _01001     88
3       _01002    371

Is there an out of the box function to do this? And if not, what would be the best way to do it. Currently I'm showing 2 columns, but the actual file has about 100 columns that I want to unpivot in this way.

That works for Office 2003, but I can't find that same Wizard in 2013. Shortcut Alt + D + P pops up a totally different screen in my localized version of Office. — Steven Thewissen, Jul 31 '14 at 17:29
Not where I'm from it seems, I'm getting a printview showing the pages my Excelsheet would be split up in. — Steven Thewissen, Jul 31 '14 at 17:36
Took some messing about with the menu's/ribbon, but found the thing and solved the problem. Thanks! — Steven Thewissen, Jul 31 '14 at 17:43

score 0 · Answer 1 · answered Jul 31 '14 at 20:08

Recently I wrote a function for my friend that does exactly what you need. Open a VBA editor, add new module, and paste it there, then just run.

Option Explicit

Sub Macro1()
    Dim i As Long
    Dim j As Long
    Dim mrow As Long
    Dim mcol As Long
    i = 0
    mcol = 4

    mrow = Cells(Rows.Count, 1).End(xlUp).Row
    Cells(1, mcol - 1).EntireColumn.Insert shift:=xlToRight

     Cells(1, 2).Copy
     Cells(1, 3).Select
     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
         :=False, Transpose:=False
     Range(Cells(mrow * i + 1, 3), Cells(mrow + mrow * i, 3)).FillDown
     i = i + 1

    While (Cells(1, mcol).Value2 <> "" And i < 200)
        '' copy data
        Range(Cells(1, mcol), Cells(mrow, mcol)).Copy
        Cells(mrow * i + 1, 2).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
         :=False, Transpose:=False
        Application.CutCopyMode = False

        '' copy dates
        Range(Cells(1, 1), Cells(mrow, 1)).Copy
        Cells(mrow * i + 1, 1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
         :=False, Transpose:=False
        Application.CutCopyMode = False

        '' fill down country
        Cells(mrow * i + 1, 2).Copy
        Cells(mrow * i + 1, 3).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
         :=False, Transpose:=False
        Range(Cells(mrow * i + 1, 3), Cells(mrow + mrow * i, 3)).FillDown

        '' delete copied data
        Range(Cells(1, mcol), Cells(mrow, mcol)).Clear

        '' increase counter
        i = i + 1
        mcol = mcol + 1

    Wend

    For j = i To 1 Step -1
         Cells(j * mrow + 1, 1).Select
         Selection.EntireRow.Delete (xlUp)
    Next j

End Sub

score -1 · Answer 2 · answered Jul 31 '14 at 18:08

Make a new sheet
Copy the first column (that is already correct) into the new sheet
Delete the first column in the original sheet.
Highlight row 1 and insert a new row above it
Fill in your headers
Insert a new column in front of column A
Paste in the first column from the new sheet you made.

Unpivoting an Excel table with 100 columns into rows

2 Answers2