How to use VBA to reshape data in excel

Question

I have a data set looks like this:

ID   | Date1| Thing1| Date2| Thing2| Date3| Thing3| Date4| Thing4|
1    |  a   |  xx   | b    |   xx  | c    | xx    | d    |  xx   |
2    |  e   |  xx   | f    |   xx  | g    | xx    | h    |  xx   |

I would like to change that to a long table like this:

ID   | Date| Thing|
 1   |  a  | xx   |
 1   |  b  | xx   |
 1   |  c  | xx   |
 1   |  d  | xx   |
 2   |  e  | xx   |
 2   |  f  | xx   |
 2   |  g  | xx   |
 2   |  h  | xx   |

I know how to do that in R but it is really confused for me in excel.

Can anyone help me set up some vba code?

Thanks in advance

Use the macro recorder to get started. It will record what you do by hand and turn it into code which you can then review in the VBA editor. Simple copy & paste should do. — nicolaus-hee, May 28 '15 at 05:21
If the column `ID` were repeated as well, you could as take a formula approach using the [idea here](http://stackoverflow.com/questions/30270031/how-to-divide-long-rows-into-multiple-smaller-rows-for-an-entire-very-large-da/30271533#30271533). — Byron Wall, May 28 '15 at 21:19

score 2 · Answer 1 · edited May 23 '17 at 12:25

There are 3 questions very similar to yours

Get all combinations of the first column's value and other column's value

Extract categories from columns into duplicated rows with new category

https://superuser.com/questions/683413/transform-horizontal-table-layout-to-vertical-table

I would adapt answer https://superuser.com/a/683439/245595 to your case, it is very easy. Some of the slight changes you need:

Extend rng_all as needed.
Instead of using icol, ncols, etc., and sweeping across columns for each ID, you waould have to use icase, ncases, etc., and sweep across cases:

ncases = ( WorksheetFunction.CountA(rng_curr) - 1 ) / 2
For icase = 1 To ncases
Change slighlty code inside the loop.
Add code for copying the third column for each case.

If non-VBA is ok, this can be used.

score 2 · Accepted Answer · answered May 28 '15 at 10:33

here one of the variant from my side

Sub test()
    Dim Key, Dic As Object, cl As Range, Data As Range, i&, n&
    Set Dic = CreateObject("Scripting.Dictionary")
    Dic.CompareMode = vbTextCompare
    i = Cells(Rows.Count, "A").End(xlUp).Row
    n = 1
    Set Data = Range("B2:B" & i & "," & "D2:D" & i & "," & "F2:F" & i & "," & "H2:H" & i)
    Dic.Add "|ID", "Date|Thing"
    For Each cl In Data
        If Cells(cl.Row, "A") <> "" Then
            Dic.Add n & "|" & Cells(cl.Row, "A"), cl.Text & "|" & cl.Offset(, 1).Text
            n = n + 1
        End If
    Next cl
    n = 1
    For Each Key In Dic
        Cells(n, "K") = Split(Key, "|")(1)
        Cells(n, "L") = Split(Dic(Key), "|")(0)
        Cells(n, "M") = Split(Dic(Key), "|")(1)
        n = n + 1
    Next Key
End Sub

output

enter image description here

Thanks I will try something like this – Lambo Jun 02 '15 at 02:19 — Lambo, Jun 02 '15 at 02:19

How to use VBA to reshape data in excel

2 Answers2

Linked