ExcelBanter - View Single Post

wrote...
how to I write out a matrix as fast as possible in excel

example
a b c
a 1 2 3
b 2 3 4
c 6 7 8

to

a a 1
a b 2
a c 3
b a 2
b b 3
b c 4
c a 6
c b 7
c c 8

Looks like you want to un-crosstab. If so, and the original table (4x4
with top-left cell blank) were named TBL, enter the top-left result
cell in, say, G1 using the following formulas.

G1:
=INDEX(TBL,1+INT((ROWS(G$1:G1)+ROWS(TBL)-2)/(ROWS(TBL)-1)),1)

H1:
=INDEX(TBL,1,2+MOD(ROWS(H$1:H1)+ROWS(TBL)-2,ROWS(TBL)-1))

I1:
=INDEX(TBL,1+INT((ROWS(I$1:I1)+ROWS(TBL)-2)/(ROWS(TBL)-1)),
2+MOD(ROWS(H$1:H1)+ROWS(TBL)-2,ROWS(TBL)-1))

Select G1:I1 and fill down into G2:I9.