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)

TEST	LOCATION	2016A	2016B	2016C	2017A	2017B	2017C	2018A	2018B	2018C
1	UK	5	6	7	8	9	10	11	12	13
2	USA	20	21	22	23	24	25	26	27	28

<tbody>
</tbody>

Table 2 (desired output)

TEST	LOCATION	A	B	C	Year
1	UK	5	6	7	2016
1	UK	8	9	10	2017
1	UK	11	12	13	2018
2	USA	20	21	22	2016
2	USA	23	24	25	2017
2	USA	26	27	28	2017

<tbody>
</tbody>

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"

v	A	B	C	D	E	F
1	TEST	LOCATION	Year	A	B	C
2	1	UK	2016	5	6	7
3	1	UK	2017	8	9	10
4	1	UK	2018	11	12	13
5	2	USA	2016	20	21	22
6	2	USA	2017	23	24	25
7	2	USA	2018	26	27	28

 

[anglais428]

Thanks Mick and Alan, both solutions work perfectly.

 

[MickG]

You're welcome

 

[alansidman]

Glad to have helped