Excel Transpose Help

M

Mac1206

Board Regular
Joined
Jun 3, 2016
Messages
184
Hello, I a have a spreadsheet of about 1600 rows in which I need to get into another format....

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Part[/TD]
[TD="width: 64"]ID[/TD]
[TD="width: 64"]Amount[/TD]
[/TR]
[TR]
[TD="class: xl63"]79123[/TD]
[TD="class: xl63"]5233122[/TD]
[TD="class: xl63"]44.18[/TD]
[/TR]
[TR]
[TD="class: xl63"]79123[/TD]
[TD="class: xl63"]11606476 [/TD]
[TD="class: xl63"]95.1[/TD]
[/TR]
[TR]
[TD="class: xl63"]79123[/TD]
[TD="class: xl63"]22346192[/TD]
[TD="class: xl63"]149.81[/TD]
[/TR]
[TR]
[TD="class: xl63"]79314[/TD]
[TD="class: xl63"]12129561[/TD]
[TD="class: xl63"]72.77[/TD]
[/TR]
[TR]
[TD="class: xl63"]79314[/TD]
[TD="class: xl63"]15504217[/TD]
[TD="class: xl63"]79.24[/TD]
[/TR]
[TR]
[TD="class: xl63"]79314[/TD]
[TD="class: xl63"]17257196[/TD]
[TD="class: xl63"]36.89[/TD]
[/TR]
[TR]
[TD="class: xl63"]79314[/TD]
[TD="class: xl63"]20765123[/TD]
[TD="class: xl63"]37.11[/TD]
[/TR]
[TR]
[TD="class: xl63"]79314[/TD]
[TD="class: xl63"]19830543[/TD]
[TD="class: xl63"]66.63[/TD]
[/TR]
[TR]
[TD="class: xl63"]79314[/TD]
[TD="class: xl63"]22166231[/TD]
[TD="class: xl63"]34.58[/TD]
[/TR]
[TR]
[TD="class: xl63"]79314[/TD]
[TD="class: xl63"]24132158[/TD]
[TD="class: xl63"]73.11[/TD]
[/TR]
</tbody>[/TABLE]


My result need to look like this and I don't have a clue to how I'm going to get through this? Thanks for your support....

[TABLE="width: 960"]
<colgroup><col width="64" span="15" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"] Part [/TD]
[TD="width: 64"]ID 1[/TD]
[TD="width: 64"] ID 2 [/TD]
[TD="width: 64"]ID 3[/TD]
[TD="width: 64"] ID 4[/TD]
[TD="width: 64"] ID 5 [/TD]
[TD="width: 64"]ID 6 [/TD]
[TD="width: 64"] ID 7[/TD]
[TD="width: 64"] Amt 1[/TD]
[TD="width: 64"]Amt 2[/TD]
[TD="width: 64"]Amt 3[/TD]
[TD="width: 64"]Amt 4[/TD]
[TD="width: 64"]Amt 5[/TD]
[TD="width: 64"]Amt 6[/TD]
[TD="width: 64"]Amt 7[/TD]
[/TR]
[TR]
[TD="align: right"]79123 [/TD]
[TD="class: xl66"]5233122[/TD]
[TD="class: xl66"] 11606476 [/TD]
[TD="class: xl66"]22346192 [/TD]
[TD][/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl67"] 44.18[/TD]
[TD="class: xl67"]95.1[/TD]
[TD="class: xl65"]149.81[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]79314 [/TD]
[TD="class: xl66"]12129561[/TD]
[TD="class: xl66"] 15504217[/TD]
[TD="class: xl66"]17257196[/TD]
[TD="class: xl66"] 20765123[/TD]
[TD="class: xl66"] 19830543 [/TD]
[TD="class: xl66"]22166231[/TD]
[TD="class: xl66"] 24132158[/TD]
[TD="class: xl66"] 72.77[/TD]
[TD="class: xl66"]79.24[/TD]
[TD="class: xl66"]36.89[/TD]
[TD="class: xl66"]37.11[/TD]
[TD="class: xl66"]66.63[/TD]
[TD="class: xl66"]34.58[/TD]
[TD="class: xl66"]73.11[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
with PowerQuery aka Get&Transform

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Part[/td][td=bgcolor:#5B9BD5]ID[/td][td=bgcolor:#5B9BD5]Amount[/td][td][/td][td=bgcolor:#70AD47]Part[/td][td=bgcolor:#70AD47]ID.1[/td][td=bgcolor:#70AD47]ID.2[/td][td=bgcolor:#70AD47]ID.3[/td][td=bgcolor:#70AD47]ID.4[/td][td=bgcolor:#70AD47]ID.5[/td][td=bgcolor:#70AD47]ID.6[/td][td=bgcolor:#70AD47]ID.7[/td][td=bgcolor:#70AD47]Amount.1[/td][td=bgcolor:#70AD47]Amount.2[/td][td=bgcolor:#70AD47]Amount.3[/td][td=bgcolor:#70AD47]Amount.4[/td][td=bgcolor:#70AD47]Amount.5[/td][td=bgcolor:#70AD47]Amount.6[/td][td=bgcolor:#70AD47]Amount.7[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
79123​
[/td][td=bgcolor:#DDEBF7]
5233122​
[/td][td=bgcolor:#DDEBF7]
44.18​
[/td][td][/td][td=bgcolor:#E2EFDA]79123[/td][td=bgcolor:#E2EFDA]5233122[/td][td=bgcolor:#E2EFDA]11606476[/td][td=bgcolor:#E2EFDA]22346192[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]
44.18​
[/td][td=bgcolor:#E2EFDA]
95.1​
[/td][td=bgcolor:#E2EFDA]
149.81​
[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
79123​
[/td][td]
11606476​
[/td][td]
95.1​
[/td][td][/td][td]79314[/td][td]12129561[/td][td]15504217[/td][td]17257196[/td][td]20765123[/td][td]19830543[/td][td]22166231[/td][td]24132158[/td][td]
72.77​
[/td][td]
79.24​
[/td][td]
36.89​
[/td][td]
37.11​
[/td][td]
66.63​
[/td][td]
34.58​
[/td][td]
73.11​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
79123​
[/td][td=bgcolor:#DDEBF7]
22346192​
[/td][td=bgcolor:#DDEBF7]
149.81​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
79314​
[/td][td]
12129561​
[/td][td]
72.77​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
79314​
[/td][td=bgcolor:#DDEBF7]
15504217​
[/td][td=bgcolor:#DDEBF7]
79.24​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
79314​
[/td][td]
17257196​
[/td][td]
36.89​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
79314​
[/td][td=bgcolor:#DDEBF7]
20765123​
[/td][td=bgcolor:#DDEBF7]
37.11​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
79314​
[/td][td]
19830543​
[/td][td]
66.63​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
79314​
[/td][td=bgcolor:#DDEBF7]
22166231​
[/td][td=bgcolor:#DDEBF7]
34.58​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
79314​
[/td][td]
24132158​
[/td][td]
73.11​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Code: 
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Part"}, {{"Count", each _, type table}}),
    List1 = Table.AddColumn(#"Grouped Rows", "ID", each Table.Column([Count],"ID")),
    List2 = Table.AddColumn(List1, "Amount", each Table.Column([Count],"Amount")),
    Extract1 = Table.TransformColumns(List2, {"ID", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Extract2 = Table.TransformColumns(Extract1, {"Amount", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Split1 = Table.SplitColumn(Extract2, "ID", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"ID.1", "ID.2", "ID.3", "ID.4", "ID.5", "ID.6", "ID.7"}),
    Split2 = Table.SplitColumn(Split1, "Amount", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Amount.1", "Amount.2", "Amount.3", "Amount.4", "Amount.5", "Amount.6", "Amount.7"}),
    Type = Table.TransformColumnTypes(Split2,{{"ID.1", type text}, {"ID.2", type text}, {"ID.3", type text}, {"ID.4", type text}, {"ID.5", type text}, {"ID.6", type text}, {"ID.7", type text}, {"Amount.1", type number}, {"Amount.2", type number}, {"Amount.3", type number}, {"Amount.4", type number}, {"Amount.5", type number}, {"Amount.6", type number}, {"Amount.7", type number}, {"Part", type text}})
in
    Type[/SIZE]

and NO this is not a macro/vba ;)
 
Upvote 0
Thanks Sandy,

I never used Power Query before so where can I find the steps to implement this Code above...
 
Upvote 0
Here is example for this M-code

Excel Transpose

go to Data - Show Queries - dbl click on the table in Workbook Queries - then look for Advanced Editor and you'll see this M-code

I hope you are not on Mac ? :)
 
Last edited:
Upvote 0
Thanks Sandy,

Thanks, I have the advance editor opened in my workbook for sheet 3, can I update it from here to the code you provided to make it work:

Sheet3_Sheet = Source{[Item="Sheet3",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sheet3_Sheet,{{"Column1", type any}, {"Column2", type any}, {"Column3", type text}, {"Column4", type any}, {"Column5", type any}, {"Column6", type text}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}})
 
Upvote 0
in my M-code are steps for your example from post#1
I don't know structure your actually table so I don't know you can or not but of course you can adapt steps or whole M-code to your needs

If headers and structure are the same as in code you can try copy/paste but if there will be one comma less or more you'll get error and you'll need to check why. In editor errors are usually highlighted.
 
Upvote 0
try with this:

Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Part"}, {{"Count", each _, type table}}),
    List1 = Table.AddColumn(#"Grouped Rows", "ID", each Table.Column([Count],"ID")),
    List2 = Table.AddColumn(List1, "Amount", each Table.Column([Count],"Amount")),
    Extract1 = Table.TransformColumns(List2, {"ID", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Extract2 = Table.TransformColumns(Extract1, {"Amount", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Reverse = Table.ReverseRows(Extract2),
    Split1 = Table.SplitColumn(Reverse, "ID", Splitter.SplitTextByAnyDelimiter({","}, QuoteStyle.Csv)),
    Split2 = Table.SplitColumn(Split1, "Amount", Splitter.SplitTextByAnyDelimiter({","}, QuoteStyle.Csv)),
    ReverseBack = Table.ReverseRows(Split2)

in
    ReverseBack[/SIZE]

Reverse moving longest string to the top position in the table and after all ReverseBack back to the original position

or post your actual file
 
Last edited:
Upvote 0
Hi Sandy,

This is what I have thus far, how do I add the List1 = Table.AddColumn(#"Grouped Rows", "ID", each Table.Column([Count],"ID")), step....

let
Source = Excel.Workbook(File.Contents("C:\Users\Mac1206\Desktop\Data.xlsx"), null, true),
Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Data_Sheet, [PromoteAllScalars=true]),
#"Grouped Rows" = Table.Group(#"Promoted Headers", {"Part"}, {{"Count", each _, type table}}),
#"Changed Type" = Table.TransformColumnTypes(#"Grouped Rows",{{"Part", Int64.Type}, {"ID", Int64.Type}, {"Amount", type number}})
in
#"Changed Type"
 
Upvote 0
Could you post link to shared (GoogleDrive, OneDrive or any similar) excel file with example of source data which contain exactly structure as in original ?
I assume expected result is like in post#1
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,823
Messages
6,181,170
Members
453,021
Latest member
Justyna P

Share this page

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