How to transpose columns in Excel by unpivoting the header row

Question

I have an excel file with about 100 columns, each one with multiple entries. The header of each column is the location for all the items in that section. I am trying to transpose this data into a two column excel, the first the item # and the second the location (header from the sheet).

Any ideas? I was doing copy paste but it is slow and prone to errors.

Here is a sample:

And I would like it to look like:

Thanks

Answer 1

I think the Power Query unpivot approach is the simplest.

Select your data and click From Table under the Data tab:

Select all 3 columns and click Unpivot Columns under the Transform tab:

Reorder the and rename the columns if desired and then Close and Load.