4

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.

RWL01
  • 243
  • 2
  • 4
  • 12
  • 1
    your problem is called un-pivot, you can find several solution both here and on the internet, e.g. http://superuser.com/questions/78439/is-it-possible-to-unpivot-or-reverse-pivot-in-excel/583083#583083, http://superuser.com/questions/683413/transform-horizontal-table-layout-to-vertical-table/684935#684935, http://superuser.com/questions/252148/i-have-a-sheet-that-has-2-cols-in-one-is-the-name-in-the-other-there-are-one-o/252871#252871 – Máté Juhász Jan 07 '16 at 12:12

2 Answers2

4

With data like:

enter image description here

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:

enter image description here

Gary's Student
  • 19,266
  • 6
  • 25
  • 39
1

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
Raystafarian
  • 21,583
  • 11
  • 60
  • 89