Transpose top row to column from chart and pull data except zeros

  • First, my apologies for maybe not clearly explaining what it is I'm struggling with in excel and not doing this in my subject line (also, I hi-jacked a 10yr old thread...doing well, doing well).


    I'm working with item numbers, dates and order amounts of the item number by dates with two different sheets in one excel file. In sheet1, Column A are the item numbers and starting on A3. In Row 2, starting on B2, the first date, which goes daily across for 20 days. The dated columns are not always next to each other, but they are all on Row 2. In the "spreadsheet" are the necessary amounts of items to order by date, some instances, 0 will be filled.


    So, I'm trying to find a VBA that will pull the dates from sheet1, Row 2 and transpose to sheet2 Column A. The next column would be the item number followed by the necessary amounts, SKIPPING the zeros.


    Examples attached of before and after or sheet1 (original) and sheet2, which explain what I'm trying to accomplish.


    Thanks in advance!

    Matt

  • Hello,


    Many contributors might be willing to give you a hand ...


    BUT ...


    Images are dead and inert objects ...:evil:


    Within an Excel Forum ... it sounds like a good idea ... well ....to attach a sample Excel File ;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • That's pretty, pretty, pretty solid. Amazing!


    I'm surprised at how short the code is...smart.


    I have one other question on the excel example. How hard would it be to add one more column next to the item numbers that would list two different values and those values would tell it which sheet to move the transpose and other data?


    Attaching the file example this time.

    ;)

    Thanks

  • 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"
  • I can say I've never used this "side" of excel. Thanks for showing that...very interesting. However, I liked the first sample, as I was able to manipulate it the example to my actual order sheet...it did take me some time to utilize it and was scratching my head, but that macro Carim got me was exactly what I was looking for...it works fabulous...Many Thanks to Carim. And thank you to you, alansidman for showing me this...didn't even know that it existed.

    Cheers

    Matt

    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"
  • I can say I've never used this "side" of excel. Thanks for showing that...very interesting. However, I liked the first sample, as I was able to manipulate it the example to my actual order sheet...it did take me some time to utilize it and was scratching my head, but that macro Carim got me was exactly what I was looking for...it works fabulous...Many Thanks to Carim. And thank you to you, alansidman for showing me this...didn't even know that it existed.

    Cheers

    Matt

    Hello Matt,


    Thanks a lot for your very kind comments :)


    Thanks to Alan for providing a solution with Power Query ....


    In my humble opinion, the VBA approach is the 'old school' way whereas Power Query offers a lot of Magic Power to the current user without the hassle of learning a language (...unless you want to dive into the M language )


    However, to fully take advantage of all the potential of Power Query, there is a learning curve ... and it does require some initial time investment and some practicing ...


    What is even more fascinating is to discover Power BI ... which is, for Microsoft, the ultimate strategic weapon not only to kill Excel ...but to kill the whole Office suite altogether ...8|

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)