Rank by date and alphabetically

[catherine_m]

Hello, I am trying to get a list of employees ranked by longest tenure, and would like to use lastnames as a "tiebreaker" for those who started on the same day. I would also like unique rank numbers, no skipping or no having multiple number 4s etc.

I can sort it in this way and just rank them 1-1000 manually but I would like to have a formula to keep live so that when people leave, or come back from an absence I won't have to re-sort and re-rank manually. Is there a formula for this?

Start date in column A and Last name in column B?

Thank you!

 

[skywriter]

Just use the Excel sort function. It's a few mouse clicks and keystrokes, if that. Do a custom sort with one column taking precedence before the other. For the ranking order numbers you can do a custom fill and if you have a thousand people it will do the work for you very quickly.

 

[catherine_m]

Thanks for the reply. I've already done the sorting myself and provided a rank based on that. I need this to be live though, because people come and go on a semi-regular basis and sometimes come back from an absence (but get credit back for time in). So what I have now is a snapshot of what things look like today, but that could change tomorrow.

I'd like to be able to just add a new row with the new information and have it fit into the rank automatically--and everything be re-ranked. I can do this just using a straight "=RANK" formula but I (1) need to not have duplicate rank numbers and (2) have alphabetical order by last name break ties.