Multiple criteria RANK using dates

[semper5]

Hello all,
I am trying to rank people based on hire dates, including prior service folks. How do I use the RANK statement for multiple columns? If a person was hired the same day as another, I would want the prior service column to be the tie-breaker. I will also add an additional column to include another tie-breaker.

Please find attached the sheet.

Thank you for your help.

 

[vletm]

semper5
Something like this?

 

[semper5]

Thank you so much! Seeing you used 'IF' and 'RANK', I will look at this when I am more awake to see how it works! Thank you for your help.

 

[semper5]

vletm,
Thank you for your help in this. based on the sheet you provided a solution, how can I add an additional column to add criteria for a tie breaker? Under the final rank column, there are still a few duplicate positions. These positions have a tie0breaker each year. Can you tell how to incorporate this?

 

[vletm]

semper5
Upload a Sample File
and an idea/solution
what to do if all (now two) parameters are same
like ranked to #3

 

[semper5]

I have attached a file with some notes. I don't understand RANK very well. Thank you for your help in this.

 

[vletm]

semper5
Things can make many ways ...
I add new H-column as TieBreak-value (now Random value); it is less than 1.
It can set also manually, but ... less than 1.
I changed K-column name to Value.
It always includes Tiebreak-value.
Now, there won't be (... of course it is still possible)
same rank-values in L-column!

 

[semper5]

Wow, thank you very much. When I get to work I will look more in depth at this!

 

[bosco_yip]

Rank with duplicate, in L3 formula copy down :

=IF(K3<>"",RANK(K3,$K$3:$K$82,1)+COUNTIF($K$3:K3,K3)-1,"")

Regards
Bosco

 

[semper5]

I just found out our rules do not permit us to use Excel RAND in order to break a tie for seniority. (Has to be a witnessed function). Is it possible for you to provide a way to highlight duplicates in order to manually enter the tie-breaker?

seniority is based on EOD. If more than 1 employee has the same EOD, the SCD will be the tie-breaker. If the SCD is the same as the EOD, then a user-entered tie-breaker must be used.

 

[vletm]

semper5 - reread my previous #7 reply slowly.
As I wrote:
I add new H-column as TieBreak-value (now Random value); it is less than 1.
It can set also manually, but ... less than 1.
(now Random value) means that OF COURSE that value can change as You would like to do, as You wrote in Your file ... Someone make it once a year or something like that!
If I had left those values as empty then there could be 'double ranked' as before, #2 Reply.
>> So, add Your OWN values as You would need! <<
Those values work only then there are 'duplicates' BEFORE that column-value!
Like: if ranks are before 'tie-breaker' with two or more just 1 (one).
then 'tie-breaker' value also would notice (as always) ...
then with 'tie-breaker' all final ranks cannot have same value!
... or only if someone has set two or more 'tie-breaker'-values same = not good!

 

[semper5]

semper5 - reread my previous #7 reply slowly.
As I wrote:
I add new H-column as TieBreak-value (now Random value); it is less than 1.
It can set also manually, but ... less than 1.
(now Random value) means that OF COURSE that value can change as You would like to do, as You wrote in Your file ... Someone make it once a year or something like that!
If I had left those values as empty then there could be 'double ranked' as before, #2 Reply.
>> So, add Your OWN values as You would need! <<
Those values work only then there are 'duplicates' BEFORE that column-value!
Like: if ranks are before 'tie-breaker' with two or more just 1 (one).
then 'tie-breaker' value also would notice (as always) ...
then with 'tie-breaker' all final ranks cannot have same value!
... or only if someone has set two or more 'tie-breaker'-values same = not good!

Gotcha! Thank you for your explanation!

Best regards,

Semper5