CODEWITHSUNDEEP
google-sheetsmaxfilteringgoogle-sheets-query
James Geddes
James Geddes

Reputation: 882

Extracting the top five maximum unique values

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?

Upvotes: 6

Views: 16417

Answers (2)

Võ Tự Đức
Võ Tự Đức

Reputation: 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.

Upvotes: -1

pnuts
pnuts

Reputation: 59460

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)

Upvotes: 7

Related Questions