How to Transpose Data with Formulas or VB

xdividerx

New Member
Joined
Sep 19, 2015
Messages
7
Hi There,

Iv'e searched the forum for transpose formulas that could assist me with my query, however, I couldn't find the right answers I was looking for.

I'm looking to transpose data from Table 1 into Table 2 (see images). At present, I am doing this manually by copying and pasting as transposed data. I have a few thousand lines of data and was hoping someone could assist in either a formula or VB code solution.

Thanks in advance.
 

Attachments

  • Table-1.PNG
    Table-1.PNG
    57.5 KB · Views: 10
  • Table-2.PNG
    Table-2.PNG
    39.3 KB · Views: 9

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
A way using Power Query.

Book2
ABCDEFGHIJKLMNOP
1Column1Column2Column3Column4Column5Column6Column7Column8Column9Column10Column11Column12Column13Column14Column15Column16
2ActualActualActualActualActualActualActualActualActualActualActualActual
3FY16FY16FY16FY16FY16FY16FY16FY16FY16FY16FY16FY16
4Q1Q1Q1Q2Q2Q2Q3Q3Q3Q4Q4Q4
5Jul-15Aug-15#####Oct-15Nov-15Dec-15Jan-16Feb-16Mar-16Apr-16May-16Jun-16
6RegionSiteKPIKPI %KPI %KPI %KPI %KPI %KPI %KPI %KPI %KPI %KPI %KPI %KPI %
7Reg-1Site-1KPI-185819171948591739887787580
8Reg-2Site-2KPI-188827080988895869196977676
9Reg-2Site-3KPI-186908382838392837380817590
10Reg-2Site-4KPI-193948872939179898072799670
11Reg-3Site-5KPI-176997772707990979196868973
12Reg-3Site-6KPI-179908896808071857288929078
13Reg-3Site-7KPI-198967484958077937772717885
14Reg-3Site-8KPI-194998293817785779683919773
15Reg-3Site-9KPI-186957795998897939671999696
16Reg-4Site-10KPI-189717297789795918692738970
17Reg-4Site-11KPI-186869576708894907494708582
18Reg-4Site-12KPI-192727994917671729976877778
19Reg-5Site-13KPI-186818198947989929584859196
20Reg-6Site-14KPI-173818873958477947478977271
21Reg-6Site-15KPI-173768177768097838177907783
22Reg-7Site-16KPI-194718977718299847590757776
23Reg-1Site-1KPI-280839181798078838982799196
24Reg-2Site-2KPI-285859691989586999496949697
25Reg-2Site-3KPI-278739194829989978291899777
26Reg-2Site-4KPI-298859783888399969584987482
27Reg-3Site-5KPI-282848299959290788588978995
28
29FYQTRDateRegionSiteKPIKPI%
30FY16Q1Jul-15Reg-1Site-1KPI-185
31FY16Q1Aug-15Reg-1Site-1KPI-181
32FY16Q1Sep-15Reg-1Site-1KPI-191
33FY16Q2Oct-15Reg-1Site-1KPI-171
34FY16Q2Nov-15Reg-1Site-1KPI-194
35FY16Q2Dec-15Reg-1Site-1KPI-185
36FY16Q3Jan-16Reg-1Site-1KPI-191
37FY16Q3Feb-16Reg-1Site-1KPI-173
38FY16Q3Mar-16Reg-1Site-1KPI-198
39FY16Q4Apr-16Reg-1Site-1KPI-187
40FY16Q4May-16Reg-1Site-1KPI-178
41FY16Q4Jun-16Reg-1Site-1KPI-175
42FY16Q1Jul-15Reg-2Site-2KPI-188
43FY16Q1Aug-15Reg-2Site-2KPI-182
44FY16Q1Sep-15Reg-2Site-2KPI-170
45FY16Q2Oct-15Reg-2Site-2KPI-180
46FY16Q2Nov-15Reg-2Site-2KPI-198
Sheet1


Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TopRow = Table.Skip(Source,1),
    UOC = Table.UnpivotOtherColumns(TopRow, {"Column3", "Column2", "Column1"}, "Attribute", "Value"),
    TopPart = Table.FirstN(UOC,36),
    BottomPart = Table.RemoveFirstN(UOC,48),
    Merge = Table.NestedJoin(BottomPart,{"Attribute"},TopPart,{"Attribute"},"BottomPart",JoinKind.LeftOuter),
    Column = Table.AddColumn(Merge, "Custom", each Table.Column([BottomPart],"Value")),
    Extract = Table.TransformColumns(Column, {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    NoBlank = Table.SelectRows(Extract, each ([Custom] <> "")),
    Split = Table.SplitColumn(NoBlank, "Custom", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3"}),
    Rename = Table.RenameColumns(Split,{{"Custom.1", "FY"}, {"Custom.2", "QTR"}, {"Custom.3", "Date"}}),
    Remove = Table.RemoveColumns(Rename,{"Attribute", "BottomPart"}),
    Reorder = Table.ReorderColumns(Remove,{"FY", "QTR", "Date", "Column1", "Column2", "Column3", "Value"}),
    RenameRest = Table.RenameColumns(Reorder,{{"Column1", "Region"}, {"Column2", "Site"}, {"Column3", "KPI"}, {"Value", "KPI%"}}),
    xDate = Table.TransformColumns(RenameRest,{"Date", each DateTime.Date(DateTime.FromText(_))})
in
    xDate
 
Upvote 0
A way using Power Query.

Book2
ABCDEFGHIJKLMNOP
1Column1Column2Column3Column4Column5Column6Column7Column8Column9Column10Column11Column12Column13Column14Column15Column16
2ActualActualActualActualActualActualActualActualActualActualActualActual
3FY16FY16FY16FY16FY16FY16FY16FY16FY16FY16FY16FY16
4Q1Q1Q1Q2Q2Q2Q3Q3Q3Q4Q4Q4
5Jul-15Aug-15#####Oct-15Nov-15Dec-15Jan-16Feb-16Mar-16Apr-16May-16Jun-16
6RegionSiteKPIKPI %KPI %KPI %KPI %KPI %KPI %KPI %KPI %KPI %KPI %KPI %KPI %
7Reg-1Site-1KPI-185819171948591739887787580
8Reg-2Site-2KPI-188827080988895869196977676
9Reg-2Site-3KPI-186908382838392837380817590
10Reg-2Site-4KPI-193948872939179898072799670
11Reg-3Site-5KPI-176997772707990979196868973
12Reg-3Site-6KPI-179908896808071857288929078
13Reg-3Site-7KPI-198967484958077937772717885
14Reg-3Site-8KPI-194998293817785779683919773
15Reg-3Site-9KPI-186957795998897939671999696
16Reg-4Site-10KPI-189717297789795918692738970
17Reg-4Site-11KPI-186869576708894907494708582
18Reg-4Site-12KPI-192727994917671729976877778
19Reg-5Site-13KPI-186818198947989929584859196
20Reg-6Site-14KPI-173818873958477947478977271
21Reg-6Site-15KPI-173768177768097838177907783
22Reg-7Site-16KPI-194718977718299847590757776
23Reg-1Site-1KPI-280839181798078838982799196
24Reg-2Site-2KPI-285859691989586999496949697
25Reg-2Site-3KPI-278739194829989978291899777
26Reg-2Site-4KPI-298859783888399969584987482
27Reg-3Site-5KPI-282848299959290788588978995
28
29FYQTRDateRegionSiteKPIKPI%
30FY16Q1Jul-15Reg-1Site-1KPI-185
31FY16Q1Aug-15Reg-1Site-1KPI-181
32FY16Q1Sep-15Reg-1Site-1KPI-191
33FY16Q2Oct-15Reg-1Site-1KPI-171
34FY16Q2Nov-15Reg-1Site-1KPI-194
35FY16Q2Dec-15Reg-1Site-1KPI-185
36FY16Q3Jan-16Reg-1Site-1KPI-191
37FY16Q3Feb-16Reg-1Site-1KPI-173
38FY16Q3Mar-16Reg-1Site-1KPI-198
39FY16Q4Apr-16Reg-1Site-1KPI-187
40FY16Q4May-16Reg-1Site-1KPI-178
41FY16Q4Jun-16Reg-1Site-1KPI-175
42FY16Q1Jul-15Reg-2Site-2KPI-188
43FY16Q1Aug-15Reg-2Site-2KPI-182
44FY16Q1Sep-15Reg-2Site-2KPI-170
45FY16Q2Oct-15Reg-2Site-2KPI-180
46FY16Q2Nov-15Reg-2Site-2KPI-198
Sheet1


Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TopRow = Table.Skip(Source,1),
    UOC = Table.UnpivotOtherColumns(TopRow, {"Column3", "Column2", "Column1"}, "Attribute", "Value"),
    TopPart = Table.FirstN(UOC,36),
    BottomPart = Table.RemoveFirstN(UOC,48),
    Merge = Table.NestedJoin(BottomPart,{"Attribute"},TopPart,{"Attribute"},"BottomPart",JoinKind.LeftOuter),
    Column = Table.AddColumn(Merge, "Custom", each Table.Column([BottomPart],"Value")),
    Extract = Table.TransformColumns(Column, {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    NoBlank = Table.SelectRows(Extract, each ([Custom] <> "")),
    Split = Table.SplitColumn(NoBlank, "Custom", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3"}),
    Rename = Table.RenameColumns(Split,{{"Custom.1", "FY"}, {"Custom.2", "QTR"}, {"Custom.3", "Date"}}),
    Remove = Table.RemoveColumns(Rename,{"Attribute", "BottomPart"}),
    Reorder = Table.ReorderColumns(Remove,{"FY", "QTR", "Date", "Column1", "Column2", "Column3", "Value"}),
    RenameRest = Table.RenameColumns(Reorder,{{"Column1", "Region"}, {"Column2", "Site"}, {"Column3", "KPI"}, {"Value", "KPI%"}}),
    xDate = Table.TransformColumns(RenameRest,{"Date", each DateTime.Date(DateTime.FromText(_))})
in
    xDate
Thank you for your solution! I am not familiar with Power Query, but I will start looking into it to see how it all works.
 
Upvote 0
Here is a macro to consider.
Put your data on "Sheet1", the results on "Sheet2"
If you prefer, before executing the macro, order the data by columns A, B and C

VBA Code:
Sub TransposeData()
  Dim a As Variant, b As Variant, i As Long, j As Long, k As Long
  
  With Sheets("Sheet1")
    a = .Range("A1", .Cells(.Range("A" & Rows.Count).End(3).Row, .Cells(1, Columns.Count).End(1).Column)).Value2
  End With
  
  ReDim b(1 To UBound(a, 1) * UBound(a, 2), 1 To 7)
  For i = 6 To UBound(a, 1)
    For j = 4 To UBound(a, 2)
      k = k + 1
      b(k, 1) = a(2, j)
      b(k, 2) = a(3, j)
      b(k, 3) = a(4, j)
      b(k, 4) = a(i, 1)
      b(k, 5) = a(i, 2)
      b(k, 6) = a(i, 3)
      b(k, 7) = a(i, j)
    Next
  Next
  
  Sheets("Sheet2").Range("A2").Resize(k, 7).Value = b
End Sub
 
Upvote 0
Here is a macro to consider.
Put your data on "Sheet1", the results on "Sheet2"
If you prefer, before executing the macro, order the data by columns A, B and C

VBA Code:
Sub TransposeData()
  Dim a As Variant, b As Variant, i As Long, j As Long, k As Long
 
  With Sheets("Sheet1")
    a = .Range("A1", .Cells(.Range("A" & Rows.Count).End(3).Row, .Cells(1, Columns.Count).End(1).Column)).Value2
  End With
 
  ReDim b(1 To UBound(a, 1) * UBound(a, 2), 1 To 7)
  For i = 6 To UBound(a, 1)
    For j = 4 To UBound(a, 2)
      k = k + 1
      b(k, 1) = a(2, j)
      b(k, 2) = a(3, j)
      b(k, 3) = a(4, j)
      b(k, 4) = a(i, 1)
      b(k, 5) = a(i, 2)
      b(k, 6) = a(i, 3)
      b(k, 7) = a(i, j)
    Next
  Next
 
  Sheets("Sheet2").Range("A2").Resize(k, 7).Value = b
End Sub
This worked a treat. Saved me hours of copying and pasting! Thank you very much.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top