Best Student

[SanthoshG]

Hi,
Thank you for all your advice on chandoo.org. I find your site very informative and easy to understand.

I'd like to ask a question about something I've been stuck on.

I have a list of about 100 students that meet for an event every week. After their individual scores are entered on their House Color (Blue, Red, Green, Yellow) tabs, I need to find the highest scorer from all the four tabs.
So I created a separate sheet to grab data from all the four sheets into one sheet and used the RANK function.

However, I am not able to extract the name of the highest scoring students onto the bottom of the sheet. I know Excel has some functionalities to find the MAX and MIN but it only identifies one student. If multiple people score the same high score, is there a way to extract all their names into one list?

Thanks!

Sincerely,

Santhosh George

 

[Nebu]

Hi:

I am not completely sure what you are looking for as you have not mentioned on what basis you need to pull the names, whether it should be based on colour or overall. I have pulled names based on overall ranking up to rank 4. If you want it based on colour you can replicate the logic I have used.

Note:formulas in column G-J.

Thanks

 

[SanthoshG]

I would like to extract the names of the highest scoring students into a separate sheet or in the bottom of the sheet which has all the names listed. Currently we are eyeballing the rank and manually entering the names of the winners.

Most of the online tools suggest a method but it will only identify one person as the winner. If multiple kids get the same score, it doesn't recognize that part.
Hope there is an easy way to list all the highest scorers and their score and rank in one list.

Thanks

 

[Nebu]

Hi:
I still could not understand what you are looking for, it would be easier if you could manually enter the output you are looking for and upload. I can replicate that using formulas.

Thanks

 

[SanthoshG]

Were you able to open the excel file I posted ?

If so there is a overall winners tab in there that pulls data from all the other sheets.
Need a formula to list all the highest scoring students into one list.

 

[Nebu]

Hi:

Is this what you are looking for?

Formulas in column P.

Thanks
Nebu

 

[SanthoshG]

Hi Nebu,
Thanks for the update. Please see another version of the file (attached). In this case, there are 3 kids with 400 score and only 1 of them is listed as "1".
Also I want to extract the names of the winners automatically to Rows A59, A60 and A61 with their respective scores in B59 to 61.

Hope the request is clear.

Thanks,

 

[Nebu]

Hi:

The Idea of the ranking formula which I had given is to avoid duplicate ranking, if you want the students with same score with same rank you can use normal rank formula (which is your column C). My Ranking formula will help to give you multiple results for the same score/rank treat column D as a helper column.

Thanks

 

[SanthoshG]

Nebu:
I understand your viewpoint. But my main question was not about ranking. I need a formula or method for excel to look at the whole results and if Rank column (Column C) says "1", I want it to spit out the corresponding name and score into A59, A60 etc.

Thanks,
Santhosh

 

[Nebu]

Hi:

That is precisely what I have done in the previously uploaded file.

Thanks

 

[SanthoshG]

Thanks Nebu. I didn't realize that you put it in Column P.

Now if I need the corresponding score to show as well, can we do that?

Thank you for your quick reply and help in resolving this issue. Much appreciated!

 

[SanthoshG]

Nebu -
Thanks for the good work on getting this issue resolved. I figured out your formula and how to manipulate and get the score next to it. I have one question, what does Column D represent? I was not able to decode what that column meant.
Please advise.

Thanks much!

- Santhosh

 

[Nebu]

Hi:

Column D is just to get unique ranking so that you will get multiple result for the lookup.

Thanks

 

[SanthoshG]

Thanks Nebu. This is a life-saver for my weekly tasks.

 

[SanthoshG]

Nebu -
I kind of ran into a wall when trying to copy the same formula for the team winners.
On the attached file - on Column A82-A85 of the tab "Sparks-Winner" please advise why the formula is not picking up Team Green even though it has the same score as Team Blue?
My apologies for repeated posts on this topic but hopefully this will be my last request for this thread.