6

I am running a Nerf league at a party and want my spreadsheet to show the top five contestants.

Contestants are allowed unlimited entries and only their top score is counted. Entries are being collected on Google Sheets and The Top 5 published on a kiosk screen.

Here is some sample data:

- **Full Name,Score**
- Test Test,3
- Test2 Test2,1
- Test3 Test3,10
- Test4 Test4,3
- Test5 Test5,42
- Test5 Test5,500
- Test6 Test6,20

Here is the formula I have so far (with thanks to tigeravatar):

=INDEX($A$2:$A$28,MATCH(1,INDEX(($B$2:$B$28=LARGE($B$2:$B$28,ROWS(I$1:I1)))*(COUNTIF(I$1:I1,$A$2:$A$28)=0),),0))

This formula shows all maximum values - if, for example, one person has 5 entries that are higher than everyone else, they will all be counted.

The "top five" must show only the entry with the most points from five different contestants.

What do I need to do to show only the top entry that each contestant has provided?

pnuts
  • 58,317
  • 11
  • 87
  • 139
James Geddes
  • 742
  • 3
  • 10
  • 35
  • 1
    In Excel, you could just a Pivot Table. Drag Name to Rows; Score to Values, and set the Values to show MAX – Ron Rosenfeld Oct 20 '14 at 18:26
  • 7
    Is it acceptable to do everything you need in Google Sheets alone? You could use something like `=QUERY(A2:B28,"select A, max(B) group by A order by max(B) desc limit 5 label max(B) ''",0)` – AdamL Oct 20 '14 at 21:58
  • 1
    Yes I would like to keep everything within the Google spreadsheet. Looks like that formula does exactly what I need! Thanks AdamL! – James Geddes Oct 21 '14 at 14:54

2 Answers2

7

Seems that the formula offered by @AdamL met the requirements: 

=QUERY(A2:B28,"select A, max(B) group by A order by max(B) desc limit 5 label max(B) ''",0)
pnuts
  • 58,317
  • 11
  • 87
  • 139
-1
=QUERY(A2:B28,"select A, max(B) group by A order by max(B) desc limit 5 label max(B) ''",0)

<= It works without a case.

if you have special character in cells in col A, the output/result of function is not right.

For example,

if value in A1 is HN_123_1/2, the output is not right. The special character here is "_" and "/".

Hope this help.

Võ Tự Đức
  • 1
  • 2