RANK.EQ - How can I avoid Duplicate Ranks
I have a large sheet with about 1500 rows. There is a subset of that data that I want to Rank. With the help of Genevieve P. I was able to create a formula to rank the items. But I have duplicate ranks. I want to avoid those duplicates.
The columns I want to use to rank are [Date Order Signed]. The subset is defined by a checkbox called [Waitlist].
I want the [Date Order Signed], to be the primary ranking. To remove Duplicates I am thinking of using the person's, which is a separate column, [Name]. The rank column is called [Rank Order]. Genevieve P Suggested creating a [Days] column, representing the age of the [Date Order Signed], to help with the rank.
In looking at my real data, there is rarely more than 2-4 dates the same in the subset of data. We are not as concerned if someone is ranked "lower" for some arbitrary reason, because it will rarely have a huge impact.
I have seen using a "CountIf as a way to remove duplicates, but my formula is complex enough that I have gotten lost.
Here is what the data looks like in a fake sheet.
I am currently using this formula to generate [Days],
=IFERROR(TODAY() - [Date Order Signed]@row, "")
This formula to [Rank Order]
=IFERROR(IF(Waitlist@row = 0, "", RANKEQ(Days@row, COLLECT(Days:Days, Waitlist:Waitlist, 1), 0)), "")
Any suggestions to rid myself of duplicate ranks? I have considered maybe trying to add a decimal to the [Days] value, but I might use days somewhere else some day.
Help Article Resources
Check out the Formula Handbook template!