3

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?

2

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
  • Thanks @2mkgz, FILTER() worked great. Since I was already using one I got the max just through the pivot table functionality (values -> add field -> rating -> summarise by: max) and the FILTER() was like "FILTER(Ratings!A:A, Ratings!D:D=D19, Ratings!B:B=A19))" - which I understood as "select beers from the ratings sheet column A, where the rating of that beer (column D) is equal to the max rating cell on this row (D19) and the brewery of that beer (column B) is equal to the brewery on this row (A19)". – George May 1 '15 at 1:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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