Rank function variations.

[GedSalter]

Hi Guys.

I run a charity competition where the lowest score wins a prize. I have over 100 players who each are in 4 categories.

this used to take a long time to collate information. I have written a program using the RANK.EQ function. This works very well. My issue is out of the 4 categories there could be a situation where 1 person may top multiple categories. Our competition states only one person can win one prize. That way more people can win. I have racked my brain how I can automatically remove from the no 1 spot on the other categories and elevate the next lowest score from a different player to the number 1 spot. So the same player cannot be in no 1 spot in all 4 categories only in 1 categories.

I hope I have explained it sufficiently.

Thank you in advanced. Hopefully I can be pointed in the right direction.

 

[jimrward]

Can you give an example of how your current data is laid out

 

[GedSalter]

Can you give an example of how your current data is lai

Can you give an example of how your current data is laid out

i have 5 columns. Column 1 is name columns 2 to 5 each have a score in it less than 100. each column 2 to 5 is a different competition. so if they win say column 2 they get relegated off top spot for the rest of the columns irrespective of score.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.