Search code examples
excelchartstranspose

Transpose Excel data for multi-Stacked bar chart

I am looking for a way to automatically transpose data from columns to rows based on merged cells. I have attached an example of what I imagine the result should look like.

I tried adding this data to the Pivot table, but it didn't help. The data will not be converted to the form I need.

enter image description here

Solution

  • Try using the following formula:

    enter image description here

    =LET(
     _Data, A2:B29,
     _ColA, SCAN(,TAKE(_Data,,1),LAMBDA(r,c,IF(c="",r,c))),
     _UniqColA, UNIQUE(_ColA),
     HSTACK(_UniqColA, IFNA(DROP(REDUCE("",_UniqColA,LAMBDA(r,c,
     VSTACK(r,TOROW(FILTER(TAKE(_Data,,-1), _ColA=c,""))))),1),"")))

    Or, Using Power Query, add the following m-code in Advanced Editor, after transforming your source range into Structured References:

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filled Down" = Table.FillDown(Source,{"Column1"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Column1"}, {{"All", each _, type table [Column1=text, Column2=any]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([All],"Index",1,1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Column1", "Column2", "Index"}, {"Column1", "Column2", "Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Custom", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded Custom", {{"Index", type text}}, "en-US")[Index]), "Index", "Column2")
in
    #"Pivoted Column"