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