Search code examples
regexgoogle-sheetsarray-formulasflattentextjoin

Textjoin and arrayformula

I am fighting to make this formula work on Gsheet.

So far I had it work by using

=TEXTJOIN(",",TRUE,(UNIQUE(IF(D2:D=S2:S,E2:H,""))))
Or
=IF(AA2:AA="Approved", TEXTJOIN(",",TRUE,unique(IF(D2:D=S2:S,E2:H,""))))

The formula works on the first line, however I'd like it to work as an array

=ArrayFormula(IF(AA2:AA="Approved", TEXTJOIN(",",TRUE,unique(IF(D2:D=S2:S,E2:H,"")))))

When I add the array as shown above, it shows some result, but not all the result and I cannot figure out what is wrong.

Any idea?

Solution

  • try:

    =INDEX(REGEXREPLACE(TRIM(SPLIT(FLATTEN(QUERY(QUERY(
 IF(B2:B="",,{B2:B&"♦", C2:C&", "&D2:D&", "&E2:E&", "&F2:F&","}), 
 "select max(Col2) where Col1 is not null 
  group by Col2 pivot Col1"),,9^9)), "♦")), ",$", ))

    enter image description here