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. 👍️