Rank order people in a sheet by Date with additional variables

Jeff Kline
Jeff Kline ✭✭
in Formulas and Functions

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.

sheet_sample_rank.JPG

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

Any Help is appreciated.

Jeff

· Share on FacebookShare on Twitter

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    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

    Screenshot 2023-04-03 at 10.48.05.png


    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)), "")


    Screenshot 2023-04-03 at 10.48.12.png


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

    Screenshot 2023-04-03 at 10.47.57.png


    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:

    Screenshot 2023-04-03 at 10.50.38.png


    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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    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

    Screenshot 2023-04-03 at 10.48.05.png


    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)), "")


    Screenshot 2023-04-03 at 10.48.12.png


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

    Screenshot 2023-04-03 at 10.47.57.png


    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:

    Screenshot 2023-04-03 at 10.50.38.png


    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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter
  • Jeff Kline
    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. :-)

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee

    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?

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter
  • Jeff Kline
    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.

    RankOrder Formula.JPG


    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee

    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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

    · Share on FacebookShare on Twitter
  • Jeff Kline
    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

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!