Macro - Convert Horizontal data into Vertical Data, 12 periods

[EhhMikey]

Hello all,

I hope you are well and would like to thank you for the 1000 of times MrExcel has saved me.

If someone knows a macro that could convert some horizontal data I have into vertical data it would save me hours of manual work. Unfortunately, I'm pretty new to creating Macros and have not become this advanced yet. I did find another post similar and tried to copy the macro but continually got the "Subscript out of range" Error.

I need to create this:
[TABLE="width: 517"]
<colgroup><col><col><col span="2"><col span="4"><col span="2"><col span="6"></colgroup><tbody>[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]P01[/TD]
[TD]P02[/TD]
[TD]P03[/TD]
[TD]P04[/TD]
[TD]P05[/TD]
[TD]P06[/TD]
[TD]P07[/TD]
[TD]P08[/TD]
[TD]P09[/TD]
[TD]P10[/TD]
[TD]P11[/TD]
[TD]P12[/TD]
[/TR]
[TR]
[TD] Data1 [/TD]
[TD] Data2 [/TD]
[TD]Data3[/TD]
[TD]Data4[/TD]
[TD] 25[/TD]
[TD] 35[/TD]
[TD] 45[/TD]
[TD] 55[/TD]
[TD] 100[/TD]
[TD] 200[/TD]
[TD] 45[/TD]
[TD] 2[/TD]
[TD] 8[/TD]
[TD] 9[/TD]
[TD] 57[/TD]
[TD] 22[/TD]
[/TR]
</tbody>[/TABLE]


Into this:
[TABLE="width: 384"]
<colgroup><col span="6"></colgroup><tbody>[TR]
[TD] Data 1 [/TD]
[TD] Data2 [/TD]
[TD]Data3[/TD]
[TD]Data4[/TD]
[TD]P1[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD] Data 1 [/TD]
[TD] Data2 [/TD]
[TD]Data3[/TD]
[TD]Data4[/TD]
[TD]P2[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD] Data 1 [/TD]
[TD] Data2 [/TD]
[TD]Data3[/TD]
[TD]Data4[/TD]
[TD]P3[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD] Data 1 [/TD]
[TD] Data2 [/TD]
[TD]Data3[/TD]
[TD]Data4[/TD]
[TD]P4[/TD]
[TD="align: right"]55[/TD]
[/TR]
[TR]
[TD] Data 1 [/TD]
[TD] Data2 [/TD]
[TD]Data3[/TD]
[TD]Data4[/TD]
[TD]P5[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD] Data 1 [/TD]
[TD] Data2 [/TD]
[TD]Data3[/TD]
[TD]Data4[/TD]
[TD]P6[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD] Data 1 [/TD]
[TD] Data2 [/TD]
[TD]Data3[/TD]
[TD]Data4[/TD]
[TD]P7[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD] Data 1 [/TD]
[TD] Data2 [/TD]
[TD]Data3[/TD]
[TD]Data4[/TD]
[TD]P8[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD] Data 1 [/TD]
[TD] Data2 [/TD]
[TD]Data3[/TD]
[TD]Data4[/TD]
[TD]P9[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD] Data 1 [/TD]
[TD] Data2 [/TD]
[TD]Data3[/TD]
[TD]Data4[/TD]
[TD]P10[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD] Data 1 [/TD]
[TD] Data2 [/TD]
[TD]Data3[/TD]
[TD]Data4[/TD]
[TD]P11[/TD]
[TD="align: right"]57[/TD]
[/TR]
[TR]
[TD] Data 1 [/TD]
[TD] Data2 [/TD]
[TD]Data3[/TD]
[TD]Data4[/TD]
[TD]P12[/TD]
[TD="align: right"]22[/TD]
[/TR]
</tbody>[/TABLE]


Anything helps!

Cheers,

Michael

 

[EhhMikey]

I'm running the macro but the work disappears after confirming the macro has run successfully, I'm wondering if I'm following the incorrect directions with the range. Here are my steps and an example of the data:

Please Select Range: [My input] -> "$A$1:$O$24"

How many columns, on the left side will repeat: [My Input] -> 3

My result says "You're good to go!"

then the workbook and VBA disappear and VBA and excel are empty templates?

 

[Repush]

This might work ..

Option Explicit
Sub make_linear()
    Dim c As Range
    Dim DataRange As Range
    Dim TopRange As Range
    Dim TargetRange As Range
    Set DataRange = [A2:A10]
    Set TargetRange = [R2]
    Set TopRange = [E1:P1]
    Dim top()
    top = TopRange.Value
    Dim i As Long
    Dim j As Long
    Dim k As Long
    Dim top_i As Long
    top_i = TopRange.Columns.Count - 1
    j = 0
    TargetRange.CurrentRegion.Clear
    For Each c In DataRange
        For i = 0 To top_i
            TargetRange.Offset(j + i, 0) = c.Offset(0, 0)
            TargetRange.Offset(j + i, 1) = c.Offset(0, 1)
            TargetRange.Offset(j + i, 2) = c.Offset(0, 2)
            TargetRange.Offset(j + i, 3) = c.Offset(0, 3)
            TargetRange.Offset(j + i, 4) = top(1, i + 1)
            For k = 0 To top_i
                TargetRange.Offset(j + k, 5) = c.Offset(0, 4 + k)
            Next k
        Next i
        j = j + top_i + 1
    Next c
End Sub

Gr. Henk

NB Make sure the ranges ar OK . .and SAVE before running

 

[Biz]

Hi M8,

Call me a sly fox, but I'm using the file which executes the macro as default address to save the file, but there's more.
I have taken the liberty to create a folder called "UnPivot Data Files", only if this folder is missing, and also finalised data file is saved as xlsx with filename Data & Format(Now(), "yyyy-MM-dd hh-mm-ss").

I'm saving Unpivoted data in a new file. Is it ok?

Based on comments "You're good to go!", the macro has worked and it has closed the files, both the macro and also finalised data files. Have a look and let me know, if it worked?

Biz

 

[EhhMikey]

(my best NEO impression) WHOOOAA!! You are a sly fox!! I've found the files and they look perfect!

Gentleman, both codes worked in their own way. I sincerely thank you both, this has worked grand and has saved me invaluable time. Thank you both for your help!! I greatly appreciate it and hope I can pass it on.

Cheers!!

 

[Biz]

(my best NEO impression) WHOOOAA!! You are a sly fox!! I've found the files and they look perfect!

Gentleman, both codes worked in their own way. I sincerely thank you both, this has worked grand and has saved me invaluable time. Thank you both for your help!! I greatly appreciate it and hope I can pass it on.

Cheers!!

You're Welcome!