Traspose matrix-style table to 3 columns in Excel
- by polarbear2k
I have a matrix-style table in excel where B1:Z1 are column headings and A2:A99 are row headings. I would like to convert this table to a 3 column table (column heading, row heading, cell value). It does not matter in what order the new table is.
A B C D A B C A B C
1 H1 H2 H3 1 H1 R1 V1 1 H1 R1 V1
2 R1 V1 V2 V3 => 2 H1 R2 V4 or 2 H2 R1 V2
3 R2 V4 V5 V6 3 H1 R3 V7 3 H3 R1 V3
4 R3 V7 V8 V9 4 H2 R1 V2 4 H1 R2 V4
5 H2 R2 V5 5 H2 R2 V5
6 H2 R3 V8 6 H3 R2 V6
7 H3 R1 V3 7 H1 R3 V7
8 H3 R2 V6 8 H2 R3 V8
9 H3 R3 V9 9 H3 R3 V8
I've been playing around with the OFFSET function to create the whole table but I feel like a combination of TRANSPOSE and V/HLOOKUP is required.
Thanks