CODEWITHSUNDEEP
google-sheetsconcatenationarray-formulasgoogle-sheets-querygoogle-query-language
Tallboy
Tallboy

Reputation: 13467

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

Upvotes: 3

Views: 3036

Answers (2)

player0
player0

Reputation: 1

=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

Upvotes: 2

Tallboy
Tallboy

Reputation: 13467

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

Upvotes: 4

Related Questions