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:
- The lowest value in NumberCol
- The value of NameCol that matched with the lowest NumberCol value.
- 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:
- 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)
- Show a metric of the average value in NumberCol in a group
- Show a metric of the count of rows in a group
- Filtering out rows before calculation of all of the above metrics based on the values in other columns (not the 3 listed above).
- 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)?