Google Sheets - efficiently aggregating one column for grouping, based on minimum value in another column?

Question

I have a sheet with upward of 1000 rows with the following schema:

GroupCol | NameCol | NumberCol

Example spreadsheet (much smaller data)

In the dataset, any given value in GroupCol is repeated 1-10 times.

I need another sheet that has one row for every unique value of GroupCol, along with these aggregated metrics for that group:

1. The lowest value in NumberCol
2. The value of NameCol that matched with the lowest NumberCol value.
3. These metrics automatically update as the original sheet is modified (if not immediately, then when sheet is opened), and update quickly instead of freezing for a couple seconds every time a single cell changes.

Ideally, it should also do the following, but they're not as important:

1. In the event of a tie for NumberCol within a group, it will list all tied names, concatenated with comma separation. (As opposed to listing only one name)
2. Show a metric of the average value in NumberCol in a group
3. Show a metric of the count of rows in a group
4. Filtering out rows before calculation of all of the above metrics based on the values in other columns (not the 3 listed above).
5. Be in flat table format to help with referencing it from other formulas.

---

What I've tried:

1. Pivot table.

This works in Excel, which allows you to add a Value Filter of Top X from an arbitrary other column. I added NumberCol to Values. For Rows I added GroupCol first and NameCol second. And then I added a Value Filter to NameCol, that takes only the Top 1 of NumberCol. After tweaking some settings I was able to get the aggregates to show as a flat table instead of a master-details grid.

But Google Sheets doesn't have the Top X feature for pivot tables as far as I can tell, so that doesn't work.

2. UNIQUE function to grab all groups for one column, and FILTER-based lookup aggregations in every other cell of the aggregation sheet.

This works, but it's slow. When editing NumberCol values in the original data, the whole application freezes up for ~3 seconds as it recalculates all the aggregate data. Additionally, if I adjust any of the aggregate formulas, I have to drag those changes into all 400+ aggregated rows, which is time consuming.

Cell A2, to fill col A with unique groups:

= UNIQUE(Data!$A$2:$A)

Column B, lowest number (fill in rowNum):

= IFERROR(SMALL(FILTER(Data!$C$2:$C, Data!$A$2:$A = A<rowNum>), 1), "")

Column C, corresponding names (fill in rowNum):

= TEXTJOIN(", ", TRUE, FILTER(Data!$B$2:$B, (Data!$A$2:$A=A2) * (Data!$C$2:$C = B2) ))

3. ARRAYFORMULA to do the same as (2) but without having separate formulas in every cell.

I can't get this to work, I think because the each cell's formula is already array-based from the FILTER function. I've also tried other lookup functions instead of FILTER but they all seem to block ARRAYFORMULA. ARRAYFORMULA just can't work as an aggregator I guess?

4. QUERY formula

The query API seems to be missing the functionalities I would need. Group By only has limited Aggregation methods, it doesn't allow using the minimum of one column to aggregate a different column. Joins are just nonexistent.

---

Any suggestions? Am I just going to have to stick with my slow and clunky solution from (2)?

Answer 1

Try:

={"Group","Lowest","Name";
sort({unique(filter(A2:A,A2:A<>"")),transpose(index(
query(if(transpose(unique(filter(A2:A,A2:A<>"")))=
filter(A2:A,A2:A<>""),filter(C2:C,A2:A<>""),),
"select min(Col"&join("), min(Col",sequence(countunique(A2:A)))&")"),2)),
transpose(substitute(substitute(trim(
query(substitute(if((transpose(unique(filter(A2:A,A2:A<>"")))=filter(A2:A,A2:A<>""))*
(index(query(if(transpose(unique(filter(A2:A,A2:A<>"")))=filter(A2:A,A2:A<>""),
filter(C2:C,A2:A<>""),),
"select min(Col"&join("), min(Col",sequence(countunique(A2:A)))&")"),2)=
filter(C2:C,A2:A<>"")),filter(B2:B,A2:A<>""),)," ","❄️"),,9^9))," ",", "),"❄️"," "))})}