I'm using Google Sheets.
I have a table of ratings of beers:
|---------------------------------------------------------------------------------|
| Beer | Brewery | Style | Rating | Country |
|---------------------------------------------------------------------------------|
| Sundown Lager | Malt Shovel Brewery | Pale Lager | 1 | Australia |
| Freudian Slip | Evil Twin Brewing | Barley Wine | 4 | USA |
| Sly Fox | Feral Brewing Co. | Golden Ale/Blond Ale | 2 | Australia |
| Drunken Monk | Rocks Brewing Co. | Golden Ale/Blond Ale | 3 | Australia |
| Chimay Bleue | Chimay | Belgian Strong Ale | 5 | Belgium |
| etc... |
|---------------------------------------------------------------------------------|
I have a pivot table that groups by brewery, and shows the COUNTUNIQUE() of each beer for that brewery (i.e. the number of beers that I've had from that brewery) and the AVERAGE() of the ratings for that brewery:
|------------------------------------------------------------|
| | COUNTUNIQUE of Beer | AVERAGE of Rating |
|------------------------------------------------------------|
| Rogue Ales | 6 | 3.67 |
| Bridge Road | 6 | 3.33 |
| Zierholz | 7 | 3.29 |
| Wig & Pen | 5 | 3.20 |
| etc... |
|------------------------------------------------------------|
I'd like to add a column that is "the top rated beer (or beers if there are multiple beers with the top rating) from this brewery" – i.e. my favorites from that brewery.
In SQL it'd be something like SELECT GROUP_CONCAT(beer) FROM beer_ratings GROUP BY brewery HAVING rating = MAX(rating)
.
If it was the maximum rating it'd be easy – I could just add a column for MAX(rating) – but I actually want the value/s of the "beer" column that match up with that MAX(rating), not the MAX(rating) itself.
Is there a way to select the maximum rated beers for each brewery in my pivot table?
Best Answer
The
FILTER
command does this nicely. Suppose this is your table.Then add
where
which find the best rating for the brewery listed in A10; and
which finds all beers from that brewery with the maximal rating. Result: