Google sheets "array_agg"... or equivalent? (join aggregation)

Currently, I have this

=QUERY(
  QUERY(
    'Raw Paste'!C2:E, "select C, count(C) where C is not null group by C order by C label count(C) ''"
  ), "WHERE Col2 >= 2")

The second QUERY() is so I can filter the aggregate function like an SQL HAVING function...

That will do this:

enter image description here

What I want to do though is next to the count, I want a 3rd column that joins the invoice numbers that are included in the aggregate.

This would be trivial with ARRAY_AGG(C) but google sheets ain't that fancy.

I've considered maybe using INDEX/MATCH somehow but I dunno.. I need to join the strings together where an item appears more than once.

C    D
111  PPP
222  OOO
222  QQQ

The output I want:

C    D
222  OOO, QQQ

2 answers

  • answered 2020-01-18 02:49 Tallboy

    It can be done by using FILTER and JOIN

    =IFERROR(JOIN(", ", FILTER(D2:D, C2:C = A3)))
    

    FILTER will look in C2:C for A2 and return the values from D2:D, which get passed to JOIN

  • answered 2020-01-18 19:10 player0

    =ARRAYFORMULA(REGEXREPLACE(TRIM({QUERY(QUERY(C:D, 
     "select C,count(C) where C is not null group by C pivot D", 0), "select Col1 offset 1", 0),
     TRANSPOSE(QUERY(TRANSPOSE(IF(ISNUMBER(QUERY(QUERY(C:D, 
     "select count(C) where C is not null group by C pivot D", 0), "offset 1", 0)), 
     QUERY(QUERY(C:D, 
     "select count(C) where C is not null group by C pivot D", 0), "limit 0", 1)&",", ))
     ,,999^99))}), ",$", ))
    

    0