Unpivoting & appending multiple workbooks

[Anonymous]

Hi,

I need to consolidate a large number of workbooks, about 30 or so, into one file

The problem is the data layout the files are coming in.

They are laid out like so, with date and unit of measurement ($ and QTY) in columns:

I want it unpivoted like so:

This would be easy if date & UOM were already merged in one cell, but since they are not, unpivoting just puts date & unit of measurement at the top of the new table.

Link to an example file with current extract layout and desired layout is here 

Any guidance would be much appreciated!

[mussaenda · Accepted solution]

Hi @Anonymous 

For the file you provided, please check the attached file.

But the trasnformation I did is not dynamic.

This is to give you an idea.

If you still need help, please let me know.

Hope this helps.

[Anonymous]

Thanks for your time! This solution did work 🙂