Find and Highlight duplicates in column

Anne NO · 02/14/23

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

Colleen Patterson · 02/15/23

@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.

Andrée Starå · 02/15/23

@Anne NO

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!

Colleen Patterson · 02/14/23

@Anne NO

I was able to get the formula to work as: =IF(COUNTIF(Column:Column, Column@row) >1,1)

Anne NO · 02/14/23

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

Matt Johnson · 02/14/23

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

Colleen Patterson · 02/14/23

https://community.smartsheet.com/discussion/comment/362145#Comment_362145

@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.

Anne NO · 02/15/23

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

Andrée Starå · 02/15/23

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!

Anne NO · 02/15/23

Hi,

Unfortunately it did not work

Best regards

Anne NO

Andrée Starå · 02/15/23

@Anne NO

Sorry! I missed one thing.

Try this one.

=IF(COUNTIF([DOCUMENT NO]:[DOCUMENT NO]; [DOCUMENT NO]@row) > 1; 1)

Anne NO · 02/15/23

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

Colleen Patterson · 02/15/23

@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.

Andrée Starå · 02/15/23

@Anne NO

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!

Find and Highlight duplicates in column

Best Answers

Answers

Help Article Resources

Categories