I need to transpose column B values where column A values are matching over the entire column.
I tested this but it’s not working:
=transpose(QUERY(A2:B,"select Col2 WHERE Col1 MATCHES '"&A2:A&"' "))
Current output:
| Item 1 | Item 2 | Item 3 | Item 4 | Item 5 |
|---|---|---|---|---|
| 7 | A1 | A1 | A2 | A3 |
| 7 | A2 | |||
| 7 | A3 | |||
| 8 | b1 | |||
| 8 | b2 | |||
| 8 | b3 | |||
| 9 | c1 | |||
| 9 | c2 | |||
| 9 | c3 | |||
| 9 | c4 |
Needed Output:
| Item 1 | Item 2 | Item 3 | Item 4 | Item 5 | Item 6 |
|---|---|---|---|---|---|
| 7 | A1 | A1 | A2 | A3 | |
| 7 | A2 | b1 | b2 | b3 | |
| 7 | A3 | c1 | c2 | c3 | c4 |
| 8 | b1 | ||||
| 8 | b2 | ||||
| 8 | b3 | ||||
| 9 | c1 | ||||
| 9 | c2 | ||||
| 9 | c3 | ||||
| 9 | c4 |
>Solution :
Here’s one approach you may test out:
=reduce(tocol(,1),unique(tocol(A2:A,1)),lambda(a,c,ifna(vstack(a,torow(filter(B:B,A:A=c))))))
