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.

1
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

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
2
  • we have several un-pivot questions on this site, why do you still take the effort to write a macro from scratch? Jan 7 '16 at 12:13
  • Yeah, I thought so, but none of the ones I found did this particular example. Also, I like writing them. Jan 7 '16 at 12:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.