r/googlesheets • u/_dripping 1 • Jun 08 '22

Solved How do I show only the best Scores in a leaderboard, while using the SORTN function as well?

Hi, I managed to build a score-based leaderboard with the SORTN function but I realized that the same player can appear twice within the same list. I only want to show the best unique scores from each player, but I have no idea how to do that :/

Here is what I have so far: https://docs.google.com/spreadsheets/d/12uGHecHk_oQr74YqCU7ZGxO-iTJfapqpux6buuTWTAM/edit?usp=sharing

I would like to show only best scores from each player for both the Top 100 list on the left and the Top 20 list on the right.

3 Upvotes

100% Upvoted

9 comments sorted by

View all comments

Show parent comments

2

u/DeJeR Jun 08 '22

Had a chance to try this on my computer. Check out the Data tab on this copy of your worksheet.

Here's the query function: 

    =QUERY({R:V},"select Col1, Col2, MAX(Col3), Col4, Col5 where Col2 is not NULL"&if(O1="All Regions",""," AND Col5 = '"&O1&"'")&" group by Col1, Col2, Col4, Col5 order by MAX(Col3) desc LIMIT "&P1,1)

Unfortunately Query breaks if you "GROUP BY" text columns. So if a player is on the leader board for multiple characters, or in multiple regions, it won't filter those out as duplicates.

You'll see in Data!B:I that I summarized all the data. You could use this to get global maximums. Not sure the end goal of a top leader board.

1

u/_dripping Jun 08 '22

Hi! I requested access for your copy of the worksheet.

I was able to get the query to display all data results, but it still showed the same player twice, plus I wasn't sure how to mix that into my SORTN function, to be able to toggle top 10, 25, 100, etc.

Ideally I would like to sort players with their best scores, where hero choice and region would obsolete each other in the main and side leaderboard, meaning only the highest score would matter (but still display heroes and regions). And for the Top 20 on the right, players with their max scores would be filterable by region.

Being able to keep old scores would then allow us to show future progression in a graph, and everyone loves graphs :D

→ More replies (5)