Transpose data skipping rows

    Dear all,


    Fairly new to macros. So have attached the input sheet and desired output. The array needs to be transposed in column with category being repeated. Also need to skip subtotal and blank rows.


    The headers are not required in the output.


    Thanks in advance

    A fairly simple excercise for Power Query. Load the data to PQ, highlight all data and then Unpivot the columns. Filter out the zeros, remove the uneeded columns. Here is the Mcode and the file for review.


    Code
    1. let
    2. Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    3. #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    4. #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] <> 0)),
    5. #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute"})
    6. in
    7. #"Removed Columns"

    Hey Glenn, A pivot based approach won't help. There are multiple sheets of different companies and also the incremental data will be added as different sheets.


    Hey Alan, The product category needs to repeated for each of the transposed figure.


    Also there are multiple sheets with similar kind of information, some rows are merged which needs to skipped, the subtotals need to skipped and blank rows also needs to be skipped.


    Looking for more formula/ macro based approach.


    Thanks in advance.


    Nitz

    Here is the new Mcode


    Code
    1. let
    2. Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    3. #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", type number}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", type number}, {"Column8", type number}, {"Column9", type number}, {"Column10", type number}, {"Column11", type number}, {"Column12", Int64.Type}, {"Column13", type number}, {"Column14", Int64.Type}, {"Column15", Int64.Type}, {"Column16", Int64.Type}, {"Column17", type number}, {"Column18", type number}, {"Column19", type number}, {"Column20", Int64.Type}, {"Column21", type number}, {"Column22", Int64.Type}, {"Column23", type number}, {"Column24", Int64.Type}, {"Column25", Int64.Type}, {"Column26", Int64.Type}, {"Column27", type number}, {"Column28", type number}, {"Column29", Int64.Type}, {"Column30", Int64.Type}, {"Column31", type number}, {"Column32", Int64.Type}, {"Column33", Int64.Type}, {"Column34", Int64.Type}, {"Column35", Int64.Type}, {"Column36", Int64.Type}, {"Column37", type number}, {"Column38", type number}, {"Column39", Int64.Type}, {"Column40", Int64.Type}, {"Column41", type number}, {"Column42", Int64.Type}, {"Column43", type number}, {"Column44", Int64.Type}, {"Column45", Int64.Type}, {"Column46", Int64.Type}, {"Column47", type number}, {"Column48", type number}, {"Column49", type number}, {"Column50", Int64.Type}, {"Column51", type number}, {"Column52", Int64.Type}, {"Column53", type number}, {"Column54", Int64.Type}, {"Column55", Int64.Type}, {"Column56", Int64.Type}, {"Column57", type number}, {"Column58", type number}, {"Column59", type number}, {"Column60", Int64.Type}, {"Column61", type number}}),
    4. #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> null and [Column1] <> "subtotal")),
    5. #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Column1"}, "Attribute", "Value"),
    6. #"Filtered Rows1" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> 0)),
    7. #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Attribute"})
    8. in
    9. #"Removed Columns"
    Code
    1. If you have multiple sheets that need to be merged into the solution, then you can add each to PQ, Append each to the other and then run your query (Mcode). This is a quick and easy reformatting that you are requesting. Do a bit of research on Power Query and you may be surprised at what you can accomplish.