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

Tags:

Best Answers

Answers

  • Colleen Patterson
    Colleen Patterson ✭✭✭✭✭✭

    @Anne NO

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

    If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

  • Anne NO
    Anne NO ✭✭

    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
    Matt Johnson ✭✭✭✭✭✭

    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

    Matt Johnson

    Sevan Technology

    Smartsheet Aligned Partner

  • Colleen Patterson
    Colleen Patterson ✭✭✭✭✭✭

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

    If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

  • Anne NO
    Anne NO ✭✭

    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å
    Andrée Starå ✭✭✭✭✭✭

    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.

  • Anne NO
    Anne NO ✭✭

    Hi,

    Unfortunately it did not work

    Best regards

    Anne NO

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 02/15/23

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

  • Anne NO
    Anne NO ✭✭

    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
    Colleen Patterson ✭✭✭✭✭✭
    Answer ✓

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

    If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

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

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!