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 :)
Answers
-
Add a created date auto column.
Create a report based off this sheet.
Filter by the newly added created date auto column.
Group by Number or Name.
You will then see the order of entries by each person in the respective order they were entered.
If the above helped, please upvote, it helps me!
-
Hi! Thanks for your response
We want the sheet to remove the older submission when a staff member submits their new availability.
Thank you
-
Then the only other way would be to create an automation workflow to move the rows to another sheet (maybe call it the sheet 'Archive') when certain criteria are met.
Create a Auto Number column called 'ID' - this will be used to see which entries are done in which order to determine the latest for you automatically, not tied to a date.
Then create a checkbox column called 'Duplicate' (this will be used to determine if an entry is older and a duplicate) - Make the column formula:
=IF(COUNTIFS(Number:Number, Number@row, ID:ID, >ID@row) > 0, 1, 0)
Then click on Automation —> Create from Template —> Move rows to another sheet when criteria are met.
- When 'Duplicate' changes to checked.
- Move rows to the archived sheet.
Congrats!
If this helped, please upvote, it helps me.
-
This is exactly what I need, thank you!
I tried following your instructions, but it only seems to recognise some of the duplicates. I haven't set up the archive workflow yet because I wanted to test that it would recognise all the duplicates first.
I have attached a screenshot of all the submissions from one individual person, and it has only picked up on the duplicates from their earliest submissions where they put the country code for their phone number.
I know it's not going to know that the first 3 rows are the same person as the following 13 rows because the numbers are different, but I can't figure out why it has not recognised the 13 submissions starting in 04 as being duplicates of eachother
-
Can you try changing the formula to check for the Name opposed to the number for me?
=IF(COUNTIFS({Nurse Name}:{Nurse Name}, {Nurse Name}@row, ID:ID, >ID@row) > 0, 1, 0)
Make sure to make it a column formula. Let me know what happens.
Please upvote my solution above to help me!
-
Hi! That seemed to work better for recognising the duplicates.
I've now set up the workflow and it sent all the older rows from person "Z" over to the archive, but there are still quite a few people that it hasn't sent the duplicates over for. I've attached a screenshot of person "J" to show this
-
it runs when triggered - did these happen before or after you set up the automation? You can also change it from when triggered to periodically so it will do it always.
-
I'm not certain as I'm not sure if it had them ticked yet when the formula was set to number. Will it be fine to delete the workflow and remake it?
-
It seems to not want to let me change it to periodic
-
Yup! That’s fine - or just change from triggered to a time period..
-
I tried removing the formula completely so that all the ticks would go away, and then re-adding it so they would have definitely ticked after the workflow was added, and I'm 99% sure it's all working correctly now!
Thank you SO much for your help!!!
-
you’re welcome! Have a great day!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
