google-sheetsgoogle-apps-scriptfiltergoogle-sheets-formula

Filter in Filter or Query/array for multiple sheets

I'm a long time reader, first time poster.

I'm having a problem showing invoice details for each company id for this sheets.

https://docs.google.com/spreadsheets/d/1iTJ8PR3snsbZKh01-yoXXAYc6jLQpjQ5n5z1wFKTRr8/edit?usp=sharing

I can get it to show invoice numbers for each company id within 1 cell with:

=JOIN(CHAR(10), FILTER(Invoices!B:B,Invoices!A:A=CompanyInfo!A2))

and i can also get it to show invoice details for each invoice numbers in 1 cell with:

=JOIN(CHAR(10), FILTER(InvDetails!B:B,InvDetails!A:A=Invoices!B2))

But i would like something like Filter in Filter

=JOIN(CHAR(10),FILTER(Invoices!B:B,Invoices!A:A=FILTER(InvDetails!B:B,InvDetails!A:A=Invoices!B2))

I think i need some query/array to get invoice details to be shown for each company within 1 cell. or maybe i need an apps-script to populate invDetails with companyID so i just can filter invDetails directly

Solution

  • Use map(), like this:

    =map(tocol(CompanyInfo!A2:A, 1), lambda(companyId, let(
  invoiceIds, filter(Invoices!B2:B, Invoices!A2:A = companyId),
  data, filter(InvDetails!A2:D, match(InvDetails!A2:A, invoiceIds, 0)),
  join(char(10), byrow(data, lambda(row, join(" ", row))))
)))

    The formula will get the results for all companies. Put it in a free column.

    See map(), tocol(), let(), filter(), match() and byrow().