VBA script for a fixed transpose

[spandan_rath]

Hi everyone!
I am looking for a vba script that would help me restructure my dataset. Currently my dataset is structured in the format shown below in the table.
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Val1[/TD]
[TD]Val2[/TD]
[TD]Val3[/TD]
[TD]Val4[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]abc[/TD]
[TD]xyz[/TD]
[TD]def[/TD]
[TD]ghi[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]123[/TD]
[TD]234[/TD]
[TD]345[/TD]
[TD]456[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]111[/TD]
[TD]222 [/TD]
[TD]333[/TD]
[TD]444[/TD]
[/TR]
</tbody>[/TABLE]

the output after the script is executed should yield: [TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Value#[/TD]
[TD]Val[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Val1[/TD]
[TD]abc[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Val2[/TD]
[TD]xyz[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Val3[/TD]
[TD]def[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Val4[/TD]
[TD]ghi[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Val1[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Val2[/TD]
[TD]234[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Val3[/TD]
[TD]345[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Val4[/TD]
[TD]456[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Val1[/TD]
[TD]111[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Val2[/TD]
[TD]222[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Val3[/TD]
[TD]333[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Val4[/TD]
[TD]444[/TD]
[/TR]
</tbody>[/TABLE]

Thanks!

 

[sheetspread]

The reverse pivot table does this:

https://www.youtube.com/watch?v=xmqTN0X-AgY

 

[MickG]

Try this for results on sheet2.

[COLOR="Navy"]Sub[/COLOR] MG19Jul50
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Ray = ActiveSheet.Cells(1).CurrentRegion
ReDim nray(1 To UBound(Ray, 1) * UBound(Ray, 2), 1 To 3)
c = 1
[COLOR="Navy"]For[/COLOR] n = 2 To UBound(Ray, 1)
    [COLOR="Navy"]For[/COLOR] Ac = 2 To UBound(Ray, 2)
        c = c + 1
        nray(c, 1) = Ray(n, 1)
        nray(c, 2) = Ray(1, Ac)
        nray(c, 3) = Ray(n, Ac)
    [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] n
nray(1, 1) = "ID": nray(1, 2) = "Value#": nray(1, 3) = "Val"
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(c, 3)
    .Value = nray
    .Borders.Weight = 2
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[spandan_rath]

The reverse pivot table does this:

https://www.youtube.com/watch?v=xmqTN0X-AgY

Thanks! This helped a lot!