1

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

GroupCol | NameCol | NumberCol

enter image description here

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.

enter image description here

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)?

3
  • can you share a link to the sheet in the screenshot?
    – user290033
    Mar 31, 2022 at 5:57
  • 1
    @ztiaa link added
    – opusGlass
    Mar 31, 2022 at 17:45
  • there's really no "easy" way to do that with a single formula. I proposed a possible solution in the answers.
    – user290033
    Mar 31, 2022 at 19:14

1 Answer 1

0

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

enter image description here

1
  • Thank you for the answer! This does work, and has the advantage that I don't need to drag my formula changes across the spreadsheet. Unfortunately the evaluation seems to run even slower :( not sure what the bottleneck is.
    – opusGlass
    Apr 1, 2022 at 2:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.