0

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:

QUERY TRANSPOSE OUTPUT

=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
Lod
  • 657
  • 1
  • 9
  • 30

1 Answers1

1

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

enter image description here

rockinfreakshow
  • 15,077
  • 3
  • 12
  • 19
  • Amazing! It works. I'll test it now on large dataset and come back to accept your solution asap. Many thanks! – Lod Aug 11 '23 at 13:50
  • Seems to works all right for my larger dataset. Many thanks again! Be well! – Lod Aug 11 '23 at 13:56