This looks to be totally illogical but it works with the example as far as my tired eyes can tell.
1./ Select the data including the column headers. Click on the Data Tab, click on the Filter button.
2./ From right to left (yes, you read it correctly) sort the columns in order A-Z
3./ Enter this formula in column J and fill down:
4./ Enter this ARRAY formula in column K and fill down (Enter with Ctrl + Shift + Enter):
5./ Enter this formula in column L and fill down:
6./ Where you want the data, copy the headers then fill in the following formulae (I made a new sheet and put the headers in row 1
7./ In A2 of the new worksheet enter this ARRAY formula and fill down:
8./ In B2 enter this ARRAY formula and fill across and down:
Here is the resultant file.
Bookmarks