Rank order people in a sheet by Date with additional variables

Jeff Kline

I want to create a column in my main sheet that will display the rank order (1,2,3,4,5, etc) of a subgroup of the people in the sheet. I will have these people displayed in a Report. This column will be based on 2 other columns from the sheet.  

The First Column defines the order of the subgroup. The column is called "Date Order Signed" and is in Date Format. The person with the oldest date in this column will be number 1, if they meet the criteria in the other column.  

The second Column is called" Waitlist" and is a checkbox. Only people in the Sheet with this box checked can receive a Rank Order number.  

I would like each person with the same date to be given a different Rank. So sub-sorted alphabetically by name if they have the same date.

So my sheet will have 1500+ rows. But only 80 or so will be on the waitlist. So I want the person to lose their Rank order number once the "Waitlist" box is unchecked. I also want the rank order to survive and be correct if someone re-sorts the list by name or some other variable.  

 I have a screenshot of a table called Fake, with Fake data.  So in the table below Mary, Sam, Jerry, Cynthia, Albert, Samantha, George, and Scott will not get a Rank Order number. John, Katy, and Harry have the same date, but should each have a different rank based on name.

I messed around with this in Excel and struggled to get it to work.

Any Help is appreciated.

Jeff

Genevieve P.

Hi @Jeff Kline

We can get most of the way there, however the alphabet criteria gets a little bit complicated. Lets start with ranking by Date... what I would do here is use a formula to subtract the Date Order Signed from Today's Date.

=TODAY() - [Date Order Signed]@row

This will then return a number. The "oldest" date will be the one with the highest number, meaning we can now use the RANKEQ function on that helper "Days" column, like so:

=IFERROR(IF(Waitlist@row = 0, "", RANKEQ(Days@row, COLLECT(Days:Days, Waitlist:Waitlist, 1), 0)), "")

This will show the Rank Order in your sheet like so:

Notice, however, that the Rank for anyone with the same date is the same number.

In order to see who comes first in this list, what I would suggest doing is then create a Report from this sheet.

In a Report you can filter by the Waitlist column to only show your 80 rows. Then you can sort by the Rank column and by the Name so that the order is alphabetical:

Even though the Rank Order will show duplicate numbers, this will allow you to see who is next on the list, and it will update in real-time if you check the Waitlist box or un-check it.

Let me know if this will work for you!

Cheers,

Genevieve

Genevieve P.

Hi @Jeff Kline

We can get most of the way there, however the alphabet criteria gets a little bit complicated. Lets start with ranking by Date... what I would do here is use a formula to subtract the Date Order Signed from Today's Date.

=TODAY() - [Date Order Signed]@row

This will then return a number. The "oldest" date will be the one with the highest number, meaning we can now use the RANKEQ function on that helper "Days" column, like so:

=IFERROR(IF(Waitlist@row = 0, "", RANKEQ(Days@row, COLLECT(Days:Days, Waitlist:Waitlist, 1), 0)), "")

This will show the Rank Order in your sheet like so:

Notice, however, that the Rank for anyone with the same date is the same number.

In order to see who comes first in this list, what I would suggest doing is then create a Report from this sheet.

In a Report you can filter by the Waitlist column to only show your 80 rows. Then you can sort by the Rank column and by the Name so that the order is alphabetical:

Even though the Rank Order will show duplicate numbers, this will allow you to see who is next on the list, and it will update in real-time if you check the Waitlist box or un-check it.

Let me know if this will work for you!

Cheers,

Genevieve

Jeff Kline

https://community.smartsheet.com/discussion/comment/369947#Comment_369947

@Genevieve P.

Thanks for the helpful suggestions. This data would be displayed in a report and the sorting by rank and name would be workable, but some viewers of the data might struggle with the duplicate ranks.

But the first issue is that the Days formula works fine. But the second formula for "Rank Order" does not. It is just blank. I put the formulas in my main sheet and the Fake Data sheet and neither worked. I made both formulas Column Formulas. I just double checked the formulas for errors and I could not find one. Column names are the same. Not certain what happened. Thoughts?

In working on this formula I figured out that SmartSheet needs a RankIf or RankIFs function. :-)

Genevieve P.

Hi @Jeff Kline

I wrapped the formula in an IFERROR which would return blank if there's a formula error. Can you try it without this to see what may be happening?

=IF(Waitlist@row = 0, "", RANKEQ(Days@row, COLLECT(Days:Days, Waitlist:Waitlist, 1), 0))

Can you post a screen capture in your test sheet showing the formula open up?

Jeff Kline

https://community.smartsheet.com/discussion/comment/370169#Comment_370169

@Genevieve P. Here is the Screenshot of the Fake Data. the formula says #Invalid Operation. It is clearly targeting the correct cells.

Thanks for your help.

Genevieve P.

Hi @Jeff Kline

Thank you for this, it definitely helps!

Do you have any errors in your Days formula column? It could error if there are blank cells or text in the Date Order Signed. One cell with an error in that column will bubble up - I can see that you're only getting "Invalid Operation" on rows that should have a number!

Try this in your Days column:

=IFERROR(TODAY() - [Date Order Signed]@row), "")

That should then resolve the error in your other column!

Cheers,

Genevieve

Jeff Kline

https://community.smartsheet.com/discussion/comment/370220#Comment_370220

@Genevieve P. I found my error. I had inserted an "= 0" in my IfError function. Now it works. Thanks so much. I am looking at trying to figure out how to stop the duplicate rank numbers. I may enter a new question about that issue.

Thanks

Jeff