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

Question

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:

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

I can do this in one formula with the query if you'll share an (editable) sample sheet of some sort.. — MattKing, Jan 18 '20 at 16:06

Tallboy · Answer 1 · 2020-01-18T17:00:23.283

3

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

edited Jan 18 '20 at 17:00

answered Jan 18 '20 at 02:49

Tallboy

12,847
13
82
173

there is a better solution if you also have value in A3 – player0 Jan 18 '20 at 12:56
If you want to post it i will mark it as the answer. I'll be doing this for many many rows – Tallboy Jan 18 '20 at 17:08
@player0 the rows its searching will be tens of thousands of lines long so performance might be nice – Tallboy Jan 18 '20 at 17:09

score 1 · Accepted Answer · answered Jan 18 '20 at 19:10

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))}), ",$", ))

answered Jan 18 '20 at 19:10

player0

124,011
12
67
124

1

wow thats wild. Can you explain what the performance gains are (just for my knowledge) – Tallboy Jan 18 '20 at 20:37
1

well, for starters its just a one formula instead 1000x per row – player0 Jan 18 '20 at 21:00

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

2 Answers2