What formula will identify duplicates in a column and only keep the most recent row?


Hi there!

Our staff fill out an "availability" form which populates a sheet with their availability for the week. I'm trying to create a formula for this sheet that will identify when a person has submitted their updated availability, and remove/hide their previous submissions.

I assume the way to do this would be identifying duplicates via the phone number column, and then only keeping the newest row according to the date column.

We cannot just filter by date, as many of our staff do not submit the form weekly, and we want their most recent submission to be visible no matter how old it is.

I've tried so many of the formulas on similar versions of this question within this forum, but cannot get any of them to work.

I have attached a screenshot with an example of the sheet layout. I would like it to identify duplicates in the "Number" column, and of those duplicates, only keep the one with the most recent date in the "Date" column.

Please dumb it down for me if you can, my understanding of Smartsheets formulas is very limited haha!

Thanks in advance for any help :)



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!