4

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?

skurt
  • 1,019
  • 2
  • 13
  • 29

2 Answers2

4

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.

momobo
  • 1,755
  • 1
  • 14
  • 19
  • thanks a lot! probalby it does solve it ;-) but I have to translate it with the right values. As my matrix is in cells A1:X88 I assume "3" is "87". what would help a lot are formulas with variable names not excact values. – skurt Jan 07 '11 at 10:53
  • maybe I mixed something up but only values on the diagonal (A1, B2, C3, D4) show up then there is #NA – skurt Jan 07 '11 at 10:57
  • 3 is the number of rows and columns of the matrix. To move the data you have to move basically everything else ;) – momobo Jan 07 '11 at 11:44
  • sorry, I test the example with your formula and it does NOT work. only value on the diagonal are copied and dragging it down works only for the first 3 lines :( another idea? – skurt Jan 07 '11 at 12:05
  • To move you must understand the meaning of formulas: the first one get repeatedly 1 2 3, so offset from A1 (that is the first cell left of 1) and cycle using mod that gives the remainder of the division for 3. The second one make the same for the row A B C (taking offset from the same cell A1). The third is just a standard double lookup on the data (A1:D4) – momobo Jan 07 '11 at 14:11
  • PS: I can't understand how your matrix could be in A1:X88, maybe in U85 X88? – momobo Jan 07 '11 at 14:17
  • My matrix has 24 columns and 88 rows so it uses the cells from A1 to X88. – skurt Jan 07 '11 at 23:28
0

I know that it was answer years ago, BUT there is a much easier way to flatten the pivot table. This is also called the unpivot or reverse pivot.

see this: https://www.youtube.com/watch?v=N3wWQjRWkJc or this: https://www.youtube.com/watch?v=pUXJLzqlEPk&list=LLzMcMocJLlJOCteGbfN3xvA&index=2

Kim Hall
  • 11
  • 2