Google Sheets ARRAYFORMULA/QUERY function to group and concat rows before date

Question

Sample Spreadsheet: https://docs.google.com/spreadsheets/d/11Kzq8cLf9ygyLfOA2rX3Hy3za_L8M_IcGDzWkacc0h0/copy

Similar question (Google Sheets function to group and concat rows): Google Sheets function to group and concat rows

I have two worksheets. First is a list of categories and dates. Second is a list of dates and categories. I am trying to find a single formula (either QUERY or ARRAYFORMULA) that concatenates the list of dates from the first worksheet for each category matched in the second, but only before the date listed on the second.

Please see the sample Spreadsheet for an example. I have a working formula that needs copied on each and every row, but I have 50,000+ rows of data, so the worksheet including this formula becomes unusable (i.e.- hangs/lags indefinitely and cannot be accessed with Google Apps Scripts or IMPORTRANGEs). Similarly, I cannot use an ARRAYFORMULA with a FILTER or QUERY inside, since running 50,000+ FILTERs or QUERYs is similarly untenable.

Thanks for any support !

Answer 1

try:

=BYROW(B2:B, LAMBDA(x, IFERROR(TEXTJOIN(", ", 1, 
 FILTER(Source!B:B, Source!A:A=x, Source!B:B<OFFSET(x,,-1))))))