Excel pivot/group/transpose

Question

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

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 to match your actual data set. — Rory, Sep 22 '22 at 15:05
it only shows 2 rows ok but the rest are !REFs. there are 6565 rows not just 2 — Boppity Bop, Sep 22 '22 at 15:59
@BoppityBop you will need to adjust the `$B$2:$C$7` to your dataset size. — Scott Craner, Sep 22 '22 at 16:18

score 1 · Accepted Answer · answered Sep 23 '22 at 09:04

1

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

answered Sep 23 '22 at 09:04

Rory

1 Answers1