Posts by alansidman

    I would do it with Power Query. Here is the Mcode and a sample file based upon what you provided.


    Files

    • Book4.xlsx

      (26.77 kB, downloaded 11 times, last: )

    Here is a VBA solution for you assuming your data starts in cell A1


    Here is an alternate solution using Power Query/Get and Transform


    Mcode


    Code
    1. let
    2. Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    3. #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"24-Sep", Int64.Type}, {"Column2", type any}, {"Column3", type any}, {"25-Sep", Int64.Type}, {"Column4", type any}, {"Column5", type any}, {"26-Sep", Int64.Type}, {"Column6", type any}, {"Column7", type any}, {"27-Sep", Int64.Type}, {"Column8", type any}, {"Column9", type any}, {"28-Sep", Int64.Type}, {"Column10", type any}, {"Column11", type any}, {"29-Sep", Int64.Type}}),
    4. #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Column1", "24-Sep", "25-Sep", "26-Sep", "27-Sep", "28-Sep", "29-Sep"}),
    5. #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Other Columns", {"Column1"}, "Attribute", "Value"),
    6. #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> 0)),
    7. #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Attribute", "Column1", "Value"})
    8. in
    9. #"Reordered Columns"

    Files

    Your post does not comply with our Forum RULES. Use code tags around code.


    Posting code between tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.


    Highlight your code and click the </> icon at the top of your post window.


    (I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)