Arrayfomula with filter on entire column

Question

I'm trying to build a Google Sheets formula to have a column that lists an array (can concatenate) of products ordered in the first order of a customer.

So for a any order with the same customer ID, the list of products will always be the same, corresponding to the first products they ever ordered.

This data is updated periodically so ideally the formula applies to the entire column.

Here is a sample sheet : https://docs.google.com/spreadsheets/d/1HvyeJPmAqcveA8rZ0IANdszGNWt9loRf08UTr3ChFjQ/edit#gid=1817801957

Is that even possible?

Thanks a lot

I tried building a formula using a filter and an arrayformula, it is shown as a tentative. Expected data is in column F but hard plugged.

Answer 1

you can arrive in the same destination by taking multiple other routes as well. for example:

=VSTACK("first_skus", BYROW(C2:C, LAMBDA(c, IFERROR(TEXTJOIN(", ", 1,
 QUERY(A2:D, "select A where D = 'First-time' and C = "&c, ))))))

where:

• VSTACK can be exchanged for {} array brackets
• BYROW can be exchanged for MAP
• IFERROR can be exchanged for IFNA
• TEXTJOIN can be exchanged for JOIN
• QUERY can be exchanged for FILTER

---

={"first_skus"; MAP(C2:C, LAMBDA(c, IFNA(JOIN(", ",
 FILTER(A2:A, D2:D="First-time", C2:C=c)))))}

• you can even omit IFNA in your sample spreadsheet (but better leave it in your real sheet as fail-safe)