matrix

L

lennertpronk

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

lennie
 
H

Harlan Grove

[email protected] 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
Click to expand...

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.
 
P

Pete_UK

I assumed your data was a 3 x 3 table of the numbers in cells A1 to C3.
You can enter the following formulae in the cells stated:

A6: =CHAR(96+INT(ROW()/3)-1)
B6: =CHAR(96 +MOD(ROW(),3)+1)
C6: =INDIRECT(CHAR(64+MOD(ROW(),3)+1)&(INT(ROW()/3)-1))

and copy down to row 14. This gives what you asked for, but it is not a
generalised solution.

Hope this helps.

Pete
 
Top