RANK.EQ - How can I avoid Duplicate Ranks

Jeff Kline · 04/04/23

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.

Thanks

Paul Newcome · 04/06/23

Does it matter if John is first and Katy is second, or does it not matter so long as you don't have duplicate ranks?

Jeff Kline · 04/06/23

https://community.smartsheet.com/discussion/comment/370650#Comment_370650

Good question. We do not care how we separately rank 2 people with the same date. So it does not matter. Just no duplicates. Someday we might add criteria. But not soon.

Paul Newcome · 04/07/23

In that case I would insert an auto-number column (called "Auto" in this example) with no special formatting. Then alongside your existing RANKQ formula (in a separate column) you can use:

=IF(Waitlist@row = 1, [Rank Order]@row + COUNTIFS([Rank Order]:[Rank Order], @cell<= [Rank Order]@row, Auto:Auto, @cell< Auto@row))

NOte: In the COUNTIFS the first argument (for the [Rank Order] column range) is "less than or equal to", but the second argument (for the Auto column range) is only "less than".

Jeff Kline · 04/07/23

https://community.smartsheet.com/discussion/comment/370762#Comment_370762

@Paul Newcome Your suggestion did not work. Sorry, it seems to be ranking all of the rows, even those that should be excluded. It handled the duplicates but then added in more material.

Here is a shot with the formula suggested. I copied and pasted since the rows are the same.

I also played around with adding a second Days row and adding a Decimal value to the number of days, with the decimal using the row number as its "starting point." That would allow the "DaysPlus" column to be a unique value. But I cannot get the decimal to work. I don't know enough about autonumbering rows. Not certain why I cannot use the value.

thanks again for the help and I would love any other suggestions.

Paul Newcome · 04/07/23

Sorry about that. I got focused on making the extra pieces work that I forgot to include the Waitlist criteria in the COUNTIFS. Try this:

=IF(Waitlist@row = 1, [Rank Order]@row + COUNTIFS(Waitlist:Waitlist, @cell = 1, [Rank Order]:[Rank Order], @cell<= [Rank Order]@row, Auto:Auto, @cell< Auto@row))

Jeff Kline · 04/07/23

https://community.smartsheet.com/discussion/comment/370817#Comment_370817

Paul, I truly appreciate your help. That still does not seem to work and I cannot diagnose why. I see the similarities to an Excel solution I found (using the countifs to increment the rank). But the outcome did not work.

I did find an alternative. Since we are ranking based on days (Days since Order was Signed), I decided to add a column that will be hidden. It will add a decimal to the column Days. The decimal is based on the auto number generated and will be "Small" enough that it will not increment the days. Then I rank order on that column and it worked. It also works in a report and holds up to sorts by name and such.

Do you see any problems with my solution? Our Sheet should never grow about 10,000 so I used 100,000 as my divisor to create the decimal.

Thanks again for the time. I think I will suggest an additional function for RANKifs.

Jeff

RANK.EQ - How can I avoid Duplicate Ranks

Answers

Help Article Resources

Categories