Identifying oldest entry on sheet, for different groups

JY12345

Hello!

I am working on a "waitlist" process, where the sheet will have multiple candidates under each work location. I need a formula to identify the oldest entry for each location, and if there are multiple entries for the earliest date, use a "score" column identify the higher score as the next candidate in line.

Example below of how it should look. I have tried MIN, COLLECT, etc formulas but just not getting it quite right. Any help is appreciated!

Paul Newcome

You would first insert a date type column (called "Earliest Date" in this example). This column would use a formula of

=MIN(COLLECT([Date Interview Conducted]:[Date Interview Conducted], [Work Location]:[Work Location], @cell = [Work Location]@row))

Then the [Next In Line] column formula would be:

=IF(AND([Date Interview Conducted]@row = [Earliest Date]@row, Score@row = MAX(COLLECT(Score:Score, [Date Interview Conducted]:[Date Interview Conducted], @cell = [Earliest Date]@row, [Work Location]:[Work Location], @cell = [Work Location]@row))), "Next!")

Paul Newcome

You would first insert a date type column (called "Earliest Date" in this example). This column would use a formula of

=MIN(COLLECT([Date Interview Conducted]:[Date Interview Conducted], [Work Location]:[Work Location], @cell = [Work Location]@row))

Then the [Next In Line] column formula would be:

=IF(AND([Date Interview Conducted]@row = [Earliest Date]@row, Score@row = MAX(COLLECT(Score:Score, [Date Interview Conducted]:[Date Interview Conducted], @cell = [Earliest Date]@row, [Work Location]:[Work Location], @cell = [Work Location]@row))), "Next!")

JY12345

Paul you are a life saver! This worked perfectly. Thank you so much!!

Paul Newcome

Happy to help. 👍️

JY12345

Bumping thread - I am trying to add one last qualifier to this formula. The formula needs to only return "Next!" if all of the previous criteria are met, AND the status column is "Ready"

Current formula not checking "Status" column -

=IF(AND([Date Interview Conducted]@row = [Earliest Date]@row, Score@row = MAX(COLLECT(Score:Score, [Date Interview Conducted]:[Date Interview Conducted], @cell = [Earliest Date]@row, [Work Location]:[Work Location], @cell = [Work Location]@row))), "Next!")

The formula Smart Sheet AI suggested to me that is not working:

=IF(AND([Date Interview Conducted]@row = [Earliest Date]@row, [Score]@row = MAX(COLLECT([Score]:[Score], [Date Interview Conducted]:[Date Interview Conducted], [Date Interview Conducted]@row = [Earliest Date]@row, [Work Location]:[Work Location], [Work Location]@row = [Work Location]@row)), CONTAINS("Ready", Status@row)), "Next!")

Paul Newcome

Try this:

=IF(AND([Date Interview Conducted]@row = [Earliest Date]@row, Score@row = MAX(COLLECT(Score:Score, [Date Interview Conducted]:[Date Interview Conducted], @cell = [Earliest Date]@row, [Work Location]:[Work Location], @cell = [Work Location]@row)), Status@row = "Ready"), "Next!")

JY12345

Hmm, now it is recognizing "ready", but only returning 1 next candidate across all work locations. Trying to have it identify the next candidate for each work location. The rows highlighted for Alabama, Arkansas, and Tennessee should be Next! for their locations.

Paul Newcome

Ok. What about this one?

=IF(AND([Date Interview Conducted]@row = [Earliest Date]@row, Score@row = MAX(COLLECT(Score:Score, [Date Interview Conducted]:[Date Interview Conducted], @cell = [Earliest Date]@row, [Work Location]:[Work Location], @cell = [Work Location]@row, Status:Status, @cell = "Ready")), Status@row = "Ready"), "Next!")