Find and Highlight duplicates in column
Hi,
I'm trying to find a formula to highlight duplicate values in a column in Smartsheet.
I think I have tried every formula that I can find online and in this Community, but all come back with #unparseable error. The column "Duplicate" in checkbox type is created and waites to be used. Similar formulas as mentioned below have been tested without any luck.
Please advise me in detail how to create this formular since I'm very new to Smartsheet.
=IF(COUNTIFS([COLUMN NAME]$1:[COLUMN NAME]@row, [COLUMN NAME]@row) > 1, 1)
=IF(COUNTIFS(COLUMN NAME:COLUMN NAME, COLUMN NAME@row)>1,1)
Best Regards
Anne NO
Best Answers
-
@Anne NO to make the formula work for the whole column, you would right click on the cell where you wrote your formula, and at the bottom is an option to click to Convert to Column Formula.
Smartsheet Community Champion and Ambassador
If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.
-
Glad we got it working!
Happy to help!
✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Answers
-
I was able to get the formula to work as: =IF(COUNTIF(Column:Column, Column@row) >1,1)
Smartsheet Community Champion and Ambassador
If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.
-
Hi Colleen,
Thank you for your answer. I'm sorry to say, it did not work. I notice that the column START is marked in your example. It does not do that in mine smartsheet. Why is that?
An other background for the formula not to work, may be that the column Document NO (START in your example) is a column with formular from all the other columns before? Could that be the reason?
Best Regards
Anne
-
Hi @Anne NO and @Colleen Patterson
I got this formula and directions from Kelsey Hayden back when she was doing "Formula of the Week" emails. This one takes a formula and conditional formatting if you want it highlighted.
One note on the 2nd option formula in the original message is that its missing the brackets around each of the "COLUMN NAME" references.
=IF(COUNTIF([Column With Duplicate]:[Column With Duplicate], [Column With Duplicate]@row) > 1, 1, 0)
What does it do? Checks for duplicate values in a column
What do I need to make it work? Checkbox column and Conditional Formatting
Directions: Enter the Formula in a Checkbox Column. Then set up conditional formatting to highlight the row when the checkbox is checked
I hope that helps.
Matt
-
@Anne NO, when you are writing your formulas in SS, once you have all of your parameters identified correctly, SS as a program will highlight the cells or columns that are involved in your formula to help you visualize which data is involved. If it is not visualizing in this manner, the most common reason is a missing bracket or comma.
If the column you are trying to check for Duplicates is Document No, you should be able to use the following:
=IF(COUNTIF([DOCUMENT NO]:[DOCUMENT NO], [DOCUMENT NO]@row) > 1, 1)
I redid my example and ran document no as a source with a formula, and it is working on my side.
Smartsheet Community Champion and Ambassador
If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.
-
Hi Colleen and Matt,
Thank you for your answers. I'm sorry, I have tried bith of your formulars, but none of them work.
This is the result I get, se screenshot above. I wonder if I'm doing something wrong. Once I have put in the formula in the column Duplicate, what do I do? Do I press enter or do I mark the column Document no.
Best Regards
Anne
-
Hi @Anne NO
I hope you're well and safe!
To add to Colleen's and Matt's excellent answers.
Try this one. (I think you typed in the same range twice, and your in a region that uses ; instead of ,)
=IF(COUNTIF([DOCUMENT NO]:[DOCUMENT NO]; [DOCUMENT NO]@row) > 1, 1)
Did that work?
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hi,
Unfortunately it did not work
Best regards
Anne NO
-
Sorry! I missed one thing.
Try this one.
=IF(COUNTIF([DOCUMENT NO]:[DOCUMENT NO]; [DOCUMENT NO]@row) > 1; 1)
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hi Andrèe,
Finally, it looks like it is working. I do not get the #Unparseable error message anymore.
Now, what do I do to make the formula work for the hole column? What is the next step for me to do?
I really appreciate you help!
BR Anne
-
@Anne NO to make the formula work for the whole column, you would right click on the cell where you wrote your formula, and at the bottom is an option to click to Convert to Column Formula.
Smartsheet Community Champion and Ambassador
If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.
-
Glad we got it working!
Happy to help!
✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!