Transposing a data table

[anglais428]

Hello,
I would like to transpose the first table into the second table. The table will always begin in Cell A1 but could have varying row and column lengths. I have been toying with VBA but cannot find an appropriate solution.

Table 1 (original)

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]TEST[/TD]
[TD]LOCATION[/TD]
[TD]2016A[/TD]
[TD]2016B[/TD]
[TD]2016C[/TD]
[TD]2017A[/TD]
[TD]2017B[/TD]
[TD]2017C[/TD]
[TD]2018A[/TD]
[TD]2018B[/TD]
[TD]2018C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]UK[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]USA[/TD]
[TD]20[/TD]
[TD]21[/TD]
[TD]22[/TD]
[TD]23[/TD]
[TD]24[/TD]
[TD]25[/TD]
[TD]26[/TD]
[TD]27[/TD]
[TD]28[/TD]
[/TR]
</tbody>[/TABLE]

Table 2 (desired output)

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]TEST[/TD]
[TD]LOCATION[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]Year[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]UK[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]2016[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]UK[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]2017[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]UK[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD]2018[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]USA[/TD]
[TD]20[/TD]
[TD]21[/TD]
[TD]22[/TD]
[TD]2016[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]USA[/TD]
[TD]23[/TD]
[TD]24[/TD]
[TD]25[/TD]
[TD]2017[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]USA[/TD]
[TD]26[/TD]
[TD]27[/TD]
[TD]28[/TD]
[TD]2017[/TD]
[/TR]
</tbody>[/TABLE]


Thanks.

 

[MickG]

Try this for results on sheet2.

[COLOR="Navy"]Sub[/COLOR] MG16Aug31
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] ray [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
ray = ActiveSheet.Cells(1).CurrentRegion
ReDim nray(1 To UBound(ray, 1) * (UBound(ray, 2) - 2) / 3, 1 To 6)
c = 1
nray(1, 1) = "TEST": nray(1, 2) = "LOCATION": nray(1, 3) = "A": nray(1, 4) = "B": nray(1, 5) = "C": nray(1, 6) = "Year"
[COLOR="Navy"]For[/COLOR] n = 2 To UBound(ray)

    [COLOR="Navy"]For[/COLOR] Ac = 3 To UBound(ray, 2) [COLOR="Navy"]Step[/COLOR] 3
         c = c + 1
        nray(c, 1) = ray(n, 1)
        nray(c, 2) = ray(n, 2)
        nray(c, 3) = ray(n, Ac)
        nray(c, 4) = ray(n, Ac + 1)
        nray(c, 5) = ray(n, Ac + 2)
        nray(c, 6) = Left(ray(1, Ac + 2), 4)
    [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(c, 6)
    .Value = nray
    .Borders.Weight = 2
    .Columns.AutoFit
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[alansidman]

An alternative is to use Power Query. Look at the link in my signature if you are unfamiliar with this concept. Here is the Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"TEST", "LOCATION"}, "Attribute", "Value"),
    #"Split Column by Position" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByPositions({0, 4}, false), {"Attribute.1", "Attribute.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Position",{{"Attribute.1", Int64.Type}, {"Attribute.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Attribute.1", "Year"}}),
    #"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Attribute.2]), "Attribute.2", "Value")
in
    #"Pivoted Column"

[table="class:thin_grid"]
[tr][td]v[/td]
[td="bgcolor:#ECF0F0, align:center"]A[/td]
[td="bgcolor:#ECF0F0, align:center"]B[/td]
[td="bgcolor:#ECF0F0, align:center"]C[/td]
[td="bgcolor:#ECF0F0, align:center"]D[/td]
[td="bgcolor:#ECF0F0, align:center"]E[/td]
[td="bgcolor:#ECF0F0, align:center"]F[/td]
[/tr][tr][td="bgcolor:#ECF0F0, align:center"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]TEST[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]LOCATION[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]Year[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]A[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]B[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]C[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]UK[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]2016[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]5[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]6[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]7[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]3[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]UK[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]2017[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]8[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]9[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]10[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]4[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]1[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]UK[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]2018[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]11[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]12[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]13[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]5[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]USA[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]2016[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]20[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]21[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]22[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]6[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]USA[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]2017[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]23[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]24[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]25[/td]
[/tr]
[tr][td="bgcolor:#ECF0F0, align:center"]7[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]2[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]USA[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]2018[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]26[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]27[/td]
[td="bgcolor:#FFFFFF, align:CENTER"]28[/td]
[/tr]
[/table]

 

[anglais428]

Thanks Mick and Alan, both solutions work perfectly.

 

[MickG]

You're welcome

 

[alansidman]

Glad to have helped