View Full Version : Ranking Based on criteria

01-05-2010, 11:24 AM

I am trying to create either a formula or code to be able to rank a list of employees based on three columns worth of data. In column A I have a list of employees in column B I have an X if they took that day off two years ago. Column C has an X if they took the day off one year ago and then In column D I have the date they started working. So based on Seniority and if they didn't take the day off either of the last two years they take first priority or rank. Then if they took off the day in 2008 then it goes by seniority to determine the rank and then those who took off in 2009 by seniority. So I have a list of individuals with different seniority and then based on if they had the day off one of the last two years then it places them behind everyone that didn't. If someone has had both years off then they are the last in ranking by seniority.

I will need it to give a ranking without duplicating any of the ranks so those that have the same seniority and variable they would be randomly ranked.

I hope this makes sense.

Thanks all,


Bob Phillips
01-05-2010, 12:38 PM
Surely, it only becomes relevant for people who joined on the same day. Does that apply?

01-05-2010, 02:16 PM
Well if the highest senior person takes that day of one of the last two years they fall to a "second level" ranking. All those that took the day off two years ago fall to the second level and those that took last year off falls to a third level ranking. Then rank all of them first for the first level ranking then the second and then the third.

Does this make sense?

Thank you,


Bob Phillips
01-05-2010, 03:04 PM
Put this in E2, copy down, and then rank by column E