Convert Array to list and copy rows that are on the same row VBA

Question

I am struggling with copying an array that has 2 different formats: one is horizontal while the other is diagonal. I want to take columns c-F and turn it into a list format and for every number it finds in c-f, i want it to copy the corresponding values in a-b. It also copies each header of the values in c-f and places in next to the new sheet where it transposes/copies the values.

Please see the photos below:

This is what I have

You can see that the dates are horizontal but in rows 8-11, they turn vertical.

I want to convert this data to copy and paste every cell value along with the corresponding records in a-b. Please see what I am trying to create:

Please note the new column headers on sheet 2 that don't exist in sheet 1.

All help would be greatly appreciated. I'm not sure how to begin. I believe these are the steps:

set a-f as a range in sheet 1
copy and paste a-b from sheet 1 onto sheet 2 for row 3
copy every single cell in each row between c-f for row 3 and repeat a-b for each value
move onto the next row
delete all blank rows in sheet 2 that have no values in the 4th column.

Thanks.

If you have access to it, [Power Query](https://support.microsoft.com/en-us/office/power-query-overview-and-learning-ed614c81-4b00-4291-bd3a-55d80767f81d) can do this quite easily using the reverse pivot functionality. — BigBen, Aug 26 '20 at 16:30
https://stackoverflow.com/questions/36365839/transpose-multiple-columns-to-multiple-rows-with-vba/36366394#36366394 — Tim Williams, Aug 26 '20 at 16:40

score 1 · Accepted Answer · answered Aug 26 '20 at 18:27

Power Query is available as a free add-in from Microsoft for Excel 2010. All you need to do:

Put cursor into table
Enter the Power Query UI
- Not sure about 2010, but in 2016+ you would select Data --> Get&Transform-->From Table/Range
- In 2010 you'll have to first download and install the add/in, then follow those instructions for PQ
Once in the UI, merely
- Select the Color and Food columns
- Select to Unpivot Other Columns
- Rename the Attribute Column --> Date
- Close and Load

M Code generated by the above process

let

//Change table name to reflect your reality
    Source = Excel.CurrentWorkbook(){[Name="Table18"]}[Content],
    
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Color", type text}, {"Food", type text}, {"1-Jan", Int64.Type}, {"1-Feb", Int64.Type}, {"1-Mar", Int64.Type}, {"1-Apr", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Color", "Food"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Date"}})
in
    #"Renamed Columns"

Convert Array to list and copy rows that are on the same row VBA

1 Answers1