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

Smart...not sure why on earth I thought that was the better way...too much staring at the ole screen perhaps?

Thanks for the nudge

See attachment...

Matt

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

Solid. I've thrown a couple of different scenarios at this macro, and no issues at all.

Many many thanks to you!

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

Quote from alansidman

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"