Change data table to horizontal format

stormania · Jul 1, 2022

Dear Excell Master

Kindly help for my excell as attach, how formula in excell to change source data table in horizontal (sheet DATA) to change format report in vertical ways (sheet REPORT) cause currently i do manually copy one by one.
Thank you

AliGW · Jul 1, 2022

Which version of Excel are you using?

stormania · Jul 1, 2022

excell 2013

AliGW · Jul 1, 2022

Try PowerQuery.

Download Power Query from Official Microsoft Download Center

Microsoft Power Query for Excel is an Excel add-in that enhances the self-service Business Intelligence experience in Excel by simplifying data discovery, access and collaboration. (Note: The Microsoft Power Query add-in for Excel 2010/2013 is no longer being updated by Microsoft. Please upgrade...

www.microsoft.com

M Code (Source):

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}, {"Destination", type text}, {"Japan", type any}, {"USA", type any}, {"Swiss", type any}, {"Singapore", type any}}),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Changed Type",1),
#"Filtered Rows" = Table.SelectRows(#"Removed Bottom Rows", each ([Japan] <> "Qty")),
#"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"No Doc", type text}, {"IND/07/01/1", Int64.Type}, {"IND/07/01/2", Int64.Type}, {"IND/07/01/3", Int64.Type}, {"IND/07/01/4", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Column1", "No Doc"}, "Attribute", "Value"),
#"Merged Queries" = Table.NestedJoin(#"Unpivoted Other Columns", {"Attribute"}, Lookup, {"Value"}, "Lookup", JoinKind.LeftOuter),
#"Expanded Lookup" = Table.ExpandTableColumn(#"Merged Queries", "Lookup", {"Attribute"}, {"Attribute.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Lookup",{{"Value", "Qty"}, {"No Doc", "Description"}, {"Attribute", "No Doc"}, {"Column1", "Code"}, {"Attribute.1", "Destination"}}),
#"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each ([Destination] = "Japan" or [Destination] = "USA")),
#"Sorted Rows" = Table.Sort(#"Filtered Rows1",{{"Destination", Order.Ascending}, {"Description", Order.Ascending}})
in
#"Sorted Rows"

M Code (Lookup):

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}, {"Destination", type text}, {"Japan", type any}, {"USA", type any}, {"Swiss", type any}, {"Singapore", type any}}),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Changed Type",1),
#"Filtered Rows" = Table.SelectRows(#"Removed Bottom Rows", each ([Japan] <> "Qty")),
#"Kept First Rows" = Table.FirstN(#"Filtered Rows",1),
#"Removed Columns" = Table.RemoveColumns(#"Kept First Rows",{"Code", "Destination"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {}, "Attribute", "Value")
in
#"Unpivoted Columns"

stormania · Jul 1, 2022

thank you for suggestion, but i am sorry cause i am a newbie in excell so i am not familiar with power query.
Maybe any common formula in excell ?
Thank you

bosco_yip · Jul 1, 2022

Here is 2 formula solutions for your reference

1. Single formula solution for Excel 2019 and up

In A5 array ("Ctrl+Shift+Enter") formula copied across and down :

=IFERROR(FILTERXML("<a>"&TEXTJOIN("",,TRANSPOSE(DATA!$A$7:$A$21)&""&TRANSPOSE(DATA!$B$7:$B$21)&""&TRANSPOSE(DATA!$C$5:$F$5)&""&TRANSPOSE(DATA!$C$7:$F$21)&""&TRANSPOSE(DATA!$C$4:$F$4))&"</a>","//b["&(ROW($A1)*5+COLUMN(A$1))-5&"]"),"")

2. Separated formula solution for Excel 2010 and up

[A5] =IF(ROW($A1)<=COUNTA(DATA!$B$7:$B$21)*COUNTA(DATA!$C$4:$F$4),INDEX(DATA!A$7:A$21,MOD(ROW($A1)-1,COUNTA(DATA!$B$7:$B$21))+1),"")

[B5] =IF($A5="","",INDEX(DATA!B$7:B$21,MOD(ROW($A1)-1,COUNTA(DATA!$B$7:$B$21))+1))

[C5] =IF($A5="","",INDEX(DATA!$C$5:$F$5,INT((ROW($A1)-1)/COUNTA(DATA!$B$7:$B$21))+1))

[D5] =IF(A5="","",INDEX(DATA!$C$7:$F$21,MOD(ROW($A1)-1,COUNTA(DATA!$B$7:$B$21))+1,INT((ROW($A1)-1)/COUNTA(DATA!$B$7:$B$21))+1))

[E5] =IF($A5="","",INDEX(DATA!$C$4:$F$4,INT((ROW($A1)-1)/COUNTA(DATA!$B$7:$B$21))+1))

All copied down

stormania · Jul 1, 2022

Amazing, Great, DONE, thank a lot

AliGW · Jul 2, 2022

shili12 said:
I believe PQ will not work with OP.
View attachment 79753

He has Excel 2013 so can use the add-in. It's available, just not being updated any longer. You have misunderstood Microsoft's message.

stormania · Jul 2, 2022

bosco_yip said:
Here is 2 formula solutions for your reference

1. Single formula solution for Excel 2019 and up

In A5 array ("Ctrl+Shift+Enter") formula copied across and down :

=IFERROR(FILTERXML("<a>"&TEXTJOIN("",,TRANSPOSE(DATA!$A$7:$A$21)&""&TRANSPOSE(DATA!$B$7:$B$21)&""&TRANSPOSE(DATA!$C$5:$F$5)&""&TRANSPOSE(DATA!$C$7:$F$21)&""&TRANSPOSE(DATA!$C$4:$F$4))&"</a>","//b["&(ROW($A1)*5+COLUMN(A$1))-5&"]"),"")

2. Separated formula solution for Excel 2010 and up

[A5] =IF(ROW($A1)<=COUNTA(DATA!$B$7:$B$21)*COUNTA(DATA!$C$4:$F$4),INDEX(DATA!A$7:A$21,MOD(ROW($A1)-1,COUNTA(DATA!$B$7:$B$21))+1),"")

[B5] =IF($A5="","",INDEX(DATA!B$7:B$21,MOD(ROW($A1)-1,COUNTA(DATA!$B$7:$B$21))+1))

[C5] =IF($A5="","",INDEX(DATA!$C$5:$F$5,INT((ROW($A1)-1)/COUNTA(DATA!$B$7:$B$21))+1))

[D5] =IF(A5="","",INDEX(DATA!$C$7:$F$21,MOD(ROW($A1)-1,COUNTA(DATA!$B$7:$B$21))+1,INT((ROW($A1)-1)/COUNTA(DATA!$B$7:$B$21))+1))

[E5] =IF($A5="","",INDEX(DATA!$C$4:$F$4,INT((ROW($A1)-1)/COUNTA(DATA!$B$7:$B$21))+1))

All copied down

View attachment 79754

Dear BY

Thanks for formula, could you explain thoese formula especially why use IF A5 ? and MOD ?, thank you

bosco_yip · Jul 2, 2022

stormania said:
Dear BY

Thanks for formula, could you explain thoese formula especially why use IF A5 ? and MOD ?, thank you

In [A5], this part of formula >>

=MOD(ROW($A1)-1,COUNTA(DATA!$B$7:$B$21))+1

become >>

=1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;1;2;3;4;5;6…..........

Return a serial number from 1 to 15 and repeating the serial number.

And

On the "Formulas" tab, in the "Formula Auditing" group, click "Evaluate Formula". Click Evaluate to examine the formula result step by step at a time.

Regards

stormania · Jul 2, 2022

bosco_yip said:
In [A5], this part of formula >>

=MOD(ROW($A1)-1,COUNTA(DATA!$B$7:$B$21))+1

become >>

=1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;1;2;3;4;5;6…..........

Return a serial number from 1 to 15 and repeating the serial number.

And

On the "Formulas" tab, in the "Formula Auditing" group, click "Evaluate Formula". Click Evaluate to examine the formula result step by step as a time.

Regards

thanks alot for clear explanation, thank you

Peter Bartholomew · Jul 2, 2022

There is the option of ditching legacy spreadsheet technology and going for

Code:

= LET(
      cd,      TOCOL(IF(qty,code)),
      descr,   TOCOL(IF(qty,description)),
      dest,    TOCOL(IF(qty,destination)),
      nd,      TOCOL(IF(qty,noDoc)),
      q,       TOCOL(qty),
      crit,    (dest="Japan")+(dest="USA"),
      SORT(FILTER(records, criteria))
  )

This formula also filters out records with zero quantity, which may or not be what is needed.

AliGW · Jul 3, 2022

But the OP has Excel 2013, so the LET and FILTER functions are not available to them.

Peter Bartholomew · Jul 3, 2022

Hi Ali, I accept your point. On the other hand, using Office 2013 is not a personal disability that needs to be treated with sensitivity. I used 2010 up to 2019 simply because I did not consider the changes from that date worth paying for. Then everything changed with dynamic arrays, to the extent that I now commit some of my pension to maintain a license.

I consider it worthwhile demonstrating to the OP and other readers what would be possible with and up to date Excel version, even if their decision is to stick with familiar legacy code.

AliGW · Jul 4, 2022

Agreed, Peter, but I think you could have made the point of your post clearer and mentioned the version of Excel you are using.

Change data table to horizontal format

stormania

Member

Attachments

AliGW

Active Member

stormania

Member

AliGW

Active Member

Download Power Query from Official Microsoft Download Center

Attachments

stormania

Member

bosco_yip

Excel Ninja

Attachments

stormania

Member

AliGW

Active Member

stormania

Member

bosco_yip

Excel Ninja

stormania

Member

Peter Bartholomew

Well-Known Member

AliGW

Active Member

Peter Bartholomew

Well-Known Member

AliGW

Active Member