Search code examples
google-sheetsconcatenationarray-formulasgoogle-sheets-querygoogle-query-language

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
Solution 
  • =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