Search code examples
excelmathformulaexcel-formula

How do transform a "matrix"-table to one line for each entry in excel

I have something like

  1  2  3
a x  o  x
b x  x  o
c o  o  o

and want to transform it into lines like

1 a x
1 b x
1 c x
2 a o
2 b x
2 c o
3 a x
3 b o
3 c o

by using a formula in the excel document. Playing with $ for assigning values for each row and column does give me proper results. Each time I have to do some manual changes to the formula. Any hint how to write it the right way?

Solution

  • Suppose that your matrix in the cells A1:D4

    in A6 put:

    =OFFSET($A$1,0,QUOTIENT(ROW()-ROW($A$6),3)+1)

    in B6 put:

    =OFFSET($A$1,MOD(ROW()-ROW($A$6),3)+1,0)

    in C6 put:

    =VLOOKUP($B6,$A$1:$D$4,MATCH($A6,$A$1:$D$1,0),FALSE)

    Den drag down (copying formulas) A6:C6 up to A14:C14

    (I translate my formulas from Italian so there could be some glitch)

    PS: 3 in the formulas refers to the number of rows (and column) of the example.