Work around for duplicates to only show the most updated row
Hi,
I have a list of names that were entered to the sheet more than once (but a different value in other columns).
I have a helper column to identify which are the duplicates, but is there a way to show only one of the updated one in a report?
Source Sheet
Test 1 & Test 2 are duplicates, but I only need the newer enter to show up in a report.
Is there a way to work around it?
Thank you for your help!
Answers
-
Hello Christina,
If you mean only the newer based on the Start Date, of course you can: you can use a column formula based on the MAX of the start date and the range of the specific Name via the COLLECT formula.
=IF(MAX(COLLECT([Start Date]:[Start Date]; Name:Name; Name@row)) = [Start Date]@row; 1)
And of course in your report filter on the flagged ones.
Please also check the screenshot.
Grtz. Maaik
-
Thanks for your quick response. This works, but if I want to do the same thing but in a column with ID#, the one that started with 0's, appears to be #INVALID OPERATION. is there a formula that works both for number and text?
Thank you so much! :)
-
Hi Christina, I would love to know how you was able to get checkbox column to work as a formula, thanks.
-
I added a helper column for ID# since the formula won't match if it's a number. =VALUE([ID#]@row)
=IF(MAX(COLLECT([Start Date]:[Start Date]; [ID# Helper]:[ID# Helper]; [ID#]@row)) = [Start Date]@row; 1)
Hope that helps.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!