Search code examples
excelpivot-tableexcel-2019

Excel pivot/group/transpose

Data presented like this (3 rows with same key 1,2 etc):

k va  vb
1 A11 B11
1 A12 B12
1 A13 B13
2 A21 B21
2 A22 B22
2 A23 B23
...

I need to group and transpose each group so I get (dont need keys even)

va1 vb1 va2 vb2 va3 vb3
A11 B11 A12 B12 A13 B13
A21 B21 A22 B22 A23 B23
...
Solution

  • You could use something like:

    =INDEX($B$2:$C$7,(ROWS($A$1:$A1)-1)*3+(COLUMNS($A$1:A$1)-1)/2+1,2-MOD(COLUMNS($A$1:A$1),2))

    Adjust the first range (the $B$2:$C$7 part) to match your actual data set