0

have data like this

table worksheet

and want result like this

enter image description here

using this formula:

=ARRAYFORMULA(REGEXREPLACE(TRIM(SPLIT(TRANSPOSE(QUERY(QUERY(
 IF(D2:D<>"", {C2:C&"♠", D2:D&","}, ),
 "select max(Col2) where Col1 is not null group by Col2 pivot Col1")
 ,,999^99)), "♠")), ",$", ))

make the result like this

enter image description here

Can someone figure it out, How to fix this formula? Thanks

player0
  • 124,011
  • 12
  • 67
  • 124
Mahardi Setyoso
  • 11
  • 1
  • 3
  • I'm not sure I understand. What's the difference between what you got and what you want? – Iamblichus Jul 01 '20 at 08:31
  • share a copy of your sheet – player0 Jul 01 '20 at 08:32
  • Add `=SORT(...)`? – kishkin Nov 05 '20 at 08:58
  • Is the issue simply that column 2 data is being treated as strings, rather than numeric, after the SPLIT function? Using VALUE to convert those values back to numeric should solve the sort order issue, shouldn't it? I'll try to come back to this, to figure out the formula... – kirkg13 Dec 03 '20 at 17:58

0 Answers0