How can I convert pivot style data into list data?-CodePudding

I essentially have data arrange like a pivot table in the blue table example and I would like to do a "reverse pivot" to have it formatted as is it in the green table. Its not an actual pivot table but its arranged in the same way. Optimally I would like to just use formulas but if its not possible, what would be the best method in VBA to achieve this? My actual data set is quite large (~350 rows by ~100 columns) and has to be updated constantly so it has to be completely automated. Any guidance is much appreciated!

Example

I've tried a number of built in excel functions but I can't seem to find something that matches my case.

CodePudding user response：

Have you considered "Unpivot" in Excel PowerQuery, as explained in this article?

This only works if the table is not formatted as an Excel pivot table, otherwise you'll get an error "A table cannot overlap a range that contains a PivotTable report, query results, protected cells, or another table."

Here's an example of data that was previously pivoted:

 -------- ------ ------ ------ 
|  SKU   |  1   |  7   |  8   |
 -------- ------ ------ ------ 
| 006101 | 0.87 | 0.87 | 0.87 |
| 006309 | 0.87 | 0.87 | 0.87 |
| 006507 | 0.87 | 0.87 | 0.87 |
| 006705 | 0.87 | 0.87 | 0.87 |
 -------- ------ ------ ------

After loading it into PowerQuery, you select the columns you want to unpivot, then go to "Transform" and select "Unpivot Columns" in the "Any Column" group. I picked columns 1, 7, 8.

This is the resulting M code:

let
    Source = Excel.CurrentWorkbook(){[Name="MyDataSource"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SKU", type text}, {"1", type number}, {"7", type number}, {"8", type number}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"SKU"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "ST"}})
in
    #"Renamed Columns"

And this is the resulting output when you load the data back into Excel (you can also just create a connection in PowerQuery, if you wish to process it further):

 -------- ---- ------- 
|  SKU   | ST | Value |
 -------- ---- ------- 
| 006101 |  1 |  0.87 |
| 006101 |  7 |  0.87 |
| 006101 |  8 |  0.87 |
| 006309 |  1 |  0.87 |
| 006309 |  7 |  0.87 |
| 006309 |  8 |  0.87 |
| 006507 |  1 |  0.87 |
| 006507 |  7 |  0.87 |
| 006507 |  8 |  0.87 |
| 006705 |  1 |  0.87 |
| 006705 |  7 |  0.87 |
| 006705 |  8 |  0.87 |
 -------- ---- -------