I have an Excel workbook with the following format:
| A | B | C | D | E |
|----|-------|-------|-------|-------|
1| | po#1 | po#2 | po#3 | po#1 |
2| | date1 | date2 | date3 | date4 |
3|sku1| qty1 | qty4 | qty7 | qty10 |
4|sku2| qty2 | qty5 | qty8 | qty11 |
5|sku3| qty3 | qty6 | qty9 | qty12 |
that I need to convert to the following format:
| A | B | C | D |
|--------|-------|-------|-------|
1| po#1 | date1 | sku1 | qty1 |
2| po#1 | date1 | sku2 | qty2 |
3| po#1 | date1 | sku3 | qty3 |
4| po#1 | date4 | sku1 | qty10 |
5| po#1 | date4 | sku2 | qty11 |
6| po#1 | date4 | sku3 | qty12 |
7| po#2 | date2 | sku1 | qty4 |
8| po#2 | date2 | sku2 | qty5 |
9| po#2 | date2 | sku3 | qty6 |
10| po#3 | date3 | sku1 | qty7 |
11| po#3 | date3 | sku2 | qty8 |
12| po#3 | date3 | sku3 | qty9 |
without using VBA.
On a copy, delete Row2 and apply the reverse pivot technique detailed (with pictures!) here. When you get to the Table, switch the order of the Row and Column columns and insert a new column between the two. Populate this with an HLOOKUP from your original data (that still has Row2). Sort ascending on Column Value. Delete the labels and, if desired, convert Table to Range.
Edit Above addresses an earlier version of the OP. To address the current version Rows1 and 2 could be replaced with the result of a formula such as =A1&"|"&A2 (based on the contents of A1 and A2 before replacement) with copying across to suit, then splitting these apart again in the Table by text manipulation with searching for |.