Google Sheets – How to Select Highest Rated Beers in a Pivot Table

google sheets

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.

       A         B       C
1    Beer    Brewery    Rating
2    abc        QQ      1
3    qwe        WW      3
4    wer        EE      5
5    ert        WW      2
6    dfg        QQ      4
7    sdf        WW      3

Then add

9   Brewery  MaxRating    Favorites
10   WW                

where

B10 = MAX(FILTER(C2:C7, B2:B7=A10) 

which find the best rating for the brewery listed in A10; and

C10 = JOIN(", ", FILTER(A2:A7, B2:B7=A10, C2:C7=B10)) 

which finds all beers from that brewery with the maximal rating. Result:

9   Brewery  MaxRating    Favorites
10   WW          3         qwe, sdf