Google sheet array formula + Join + Filter

Question

Does anyone know how I could expand the join+filter with arrayformula? I need it to automatically expand instead of sliding the formulae into new rows.

Tried to search this problem but couldn't find a solution for myself.

Copy to sheet itself

Problem in question highlighted in orange.

Thanks a lot!

question from:https://stackoverflow.com/questions/66067714/google-sheet-array-formula-join-filter

Answer 1

JOIN is not supported under ARRAYFORMULA

use in row 2:

=ARRAYFORMULA(IFNA(REGEXREPLACE(VLOOKUP(A2:A, 
 SPLIT(TRIM(FLATTEN(QUERY(TRANSPOSE(QUERY(FILTER(
 {A2:A&"×", TEXT(B2:B, "dd.mm.yyyy hh:mm:ss")&" ;", B2:B}, J2:J<>""), 
 "select Col1,max(Col2) group by Col1 pivot Col3")),,
 9^9))), "×"), 2, 0), "^ |;$", )))