How To Turn Two-Way Data Table Into Pivot Table

Here!

In G2

=INDEX($A$2:$A$13,ROUNDUP(ROWS($K$2:K2)/4,0))

In H2 -

=INDEX($B$1:$E$1,1,COUNTIF($K$2:K2,K2))

In I2 -

=INDEX($B$2:$E$13,MATCH(K2,$A$2:$A$13,0),MATCH(L2,$B$1:$E$1,0))

Drag all the 3 formulas down as required..

If you want to do it without formulas, you can use a pivot table to reverse the data. The steps may be a bit overwhelming at first, but it's not all that hard. Here is a Youtube walk-through

https://www.youtube.com/watch?v=N3wWQjRWkJc

If you don't see the Pivot Table Wizard in your ribbon, use the keyboard shortcut Alt-D-P

Of course, with Excel 2010 and later, you can do the whole thing with a few clicks if you have Power Query, a free add-in from Microsoft for Excel 2010 and 2013, and built into the Excel 2016 Data ribbon. The steps would be

- load the table into the Query Editor window
- set first row as header (if it isn't correct already)
- select the first column and click "Unpivot other columns"
- save and load to workbook

Power Query rocks.

cheers, teylyn