Transform multiple columns in Excel table into multiple rows

Question

I have an excel table in the following format.

ID Date1 Date2 Date3  
1  1/1   1/2   1/3
2  1/2   1/3   1/1
3  1/3   1/2   1/4

Is there a way to change it into this format?

ID Date
1  1/1
1  1/2
1  1/3
2  1/2
2  1/3
2  1/1
3  1/3
3  1/2
3  1/4

I'm open to using VBA, PowerQuery, PowerPivot, data model, etc. Is there a technical term I should use when searching for answers to this problem (normalizing, transforming, etc).

Thanks for any help.

Answer 1

With data like:

Pick a cell and enter:

=ROUNDUP(ROWS($1:1)/3,0)

and in the adjacent cell enter:

=OFFSET($B$2,ROUNDUP(ROWS($1:1)/3,0)-1,MOD(ROWS($1:1)-1,3))

copy these cells downwards and apply the proper format to the second column to see:

Answer 2

If you have a whole bunch and you'd rather use VBA, this will work

Option Explicit

Sub unpivot()
Dim lastrow As Integer
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Dim lastcol As Integer
lastcol = Cells(1, Columns.Count).End(xlToLeft).Column

Dim r As Integer
Dim c As Integer


For r = lastrow To 2 Step -1

    For c = lastcol To 3 Step -1
        If Cells(r, c) <> "" Then
            Rows(r + 1).Insert
            Cells(r + 1, 1) = Cells(r, 1)
            Cells(r + 1, 2) = Cells(r, c)
            Cells(r, c).Clear
        Else: Rows(r).Delete
        End If
    Next

Next

End Sub