Excel Table Transform

alm958 

Power Query is the easiest way to achieve the desired output.

There are two tabs in the attached...

1) Original Data: Which contains your raw data. I converted this data into an Excel Table and named it Data.

2) Transformed Data: Which contains the data in the desired format.

Follow these steps to get the data in the desired format on Transformed Data Sheet.

1) Click inside the Table "Data" on the Original Data Sheet.

2) Go to Data Tab --> Get Data --> From Table/Range. Power Query Editor will be popped up with the data after this step.

3) On Power Query Editor, select the first column by clicking on the column header then hold down the Shift key and click the last column header. This will select all the columns.

4) Now Right click on one column header of the selected columns and choose "Unpivot Columns".

5) Now double click the columns headers and rename them as PO# and Serial Numbers.

6) Sort the PO# columns in Ascending order.

7) Go to Home Table and click on Close & Load.

The data will be transferred to a New Sheet in the form of an Excel Table. Rename the Sheet Tab as per your requirement.

This is dynamic solution i.e. if you delete or add the data in the table on Original Data Sheet, come back to the Table returned on the New Sheet by Power Query, right click on any cell in that table and choose Refresh and the Table will be updated accordingly.

Please find the attached for more details.