How to convert multi column excel table to single column with header attached

Question

Hi can anyone let me know if there is a good way to convert table 1 to table 2 in excel? this is just a example the actual data is in thousands of rows.

Thank you!

Answer 1

If you have Excel O365, you could opt for:

Formula in E1:

=SORT(TRANSPOSE(CHOOSE({1,2},TRANSPOSE(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",,A2:C4&"</s><s>"&A1:C1)&"</s></t>","//s[position() mod 2 = 1]")),TRANSPOSE(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",,A2:C4&"</s><s>"&A1:C1)&"</s></t>","//s[position() mod 2 = 0]")))),1,1)

Answer 2

You can use Power Query, available in Excel 2010+

It is a part of Excel 2016+ and available as a free Microsoft provided add-in in the earlier versions.

• Data / Get & Transform / From Table/Range
• If A, B and C are not the Headers then (In the Power Query UI)
  • Home / Transform / Use First Row as Headers
• Select all the columns and
  • Transform / Any Column / Unpivot Columns
• Sort by Attribute and Value, in that order
• Move the Value column to the first column position
• Home / Close / Close & Load

All of the above steps can be done from the Power Query UI, but here is the generated M-Code

M-Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    #"Sorted Rows" = Table.Sort(#"Unpivoted Columns",{{"Attribute", Order.Ascending}, {"Value", Order.Ascending}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"Value", "Attribute"})
in
    #"Reordered Columns"