Checking for Duplicates and then Checking for the highest ID Code.
Hi there!
We use Smart Sheet for tracking multiple components of our department. On one sheet, I want a formula to check for duplicate names and then look at the ID Code Column and I want a box to be checked for any ID Code that isn't the highest of the duplicates.
I have gotten the first part of the formula to work. I have a column where the box is checked if there is a duplicate name. I use the formula below:
=IF(COUNTIF([Contractor Name]:[Contractor Name], [Contractor Name]@row) > 1, 1, 0)
I can't get the second part of the formula to work in where it then looks at the ID Code and does not check the box for the highest ID Code.
Any suggestions would be appreciated!
Best Answer
-
You will need a helper column that pulls in the highest id code for the name on each row.
=MAX(COLLECT([ID Code]:[ID Code], [Contractor Name]:[Contractor Name], @cell = [Contractor Name]@row))
Then the flag formula would look something like this:
=IF([ID Code]@row< [Helper Column]@row, 1)
Answers
-
=IF(COUNTIF([Contractor Name]7:[Contractor Name]110,[Contractor Name]@row)>1, NOT(MAX([ID Code]7:[ID Code]110,[ID Code]@row))1,0)
I also tried this formula and was returned as formula syntax isn't quite right.
-
You will need a helper column that pulls in the highest id code for the name on each row.
=MAX(COLLECT([ID Code]:[ID Code], [Contractor Name]:[Contractor Name], @cell = [Contractor Name]@row))
Then the flag formula would look something like this:
=IF([ID Code]@row< [Helper Column]@row, 1)
-
This did exactly what I needed it to!! Thank you so much for your help!!!!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!