Mark Duplicate value

12/05/18 Edited 12/09/19

Hello team



can someone suggest a simple way to mark cell (or rows) that one of the columns with Duplicate value



thanks in advance

oded

Previous1

Comments

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi Oded,

    Try this.

    This formula will check one column for duplicates and check the box in a checkbox column. Place the formula in this helper column. You can then use conditional formatting to format the cell(s) or row(s)-

    =IF(COUNTIF([Column Name]:[Column Name]; [Column Name]@row) > 1; 1)    

    The same version but with the below changes for your and others convenience.  

    =IF(COUNTIF([Column Name]:[Column Name], [Column Name]@row) > 1, 1)

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    Would that work?

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • many thanks

    it's worked as expected ! 100% !

     

    thanks !

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Great!

    Happy to help!

    Best,

    Andrée

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Hello!

    What if I wanted to compare one column to a different column checking for duplicates?

    I would want a check in the "checkbox column" if "column A" and "column B" had the same information. Is this possible?

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi,

    Not sure I follow!

    Can you elaborate?

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • I tried this and I am getting an #INVALID OPERATION error. I am in the United States.

    My row is called Company

    My formula is below:

    =IF(COUNTIF([Company]:[Company], [Company]@row, >1,1)

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi @Christopher Pretty,

    Try something like this.

    =IF(COUNTIF(Company:Company, [email protected]) > 1, 1)
    

    Did that work?

    I hope that helps!

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    ✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Andree,

    Im sorry - but where does this formula need to be typed? Im completely new to smartsheets

    I've inserted a new column and put the formula there, but i get #UNPARSEABLE

  • Hi @Kabir Luthra

    Inserting this into a new column is right!

    You'll need to update the [Column Name] to be specific to your column name. If you're still getting an error, it would be helpful to see a screen capture with the formula open in the sheet, but please block out any sensitive data!

    Cheers,

    Genevieve

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    @Kabir Luthra

    I saw that Genevieve answered already!

    Let me know if I can help with anything else!

    Best,

    Andrée

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • I used this formula and it works great. Thank You!

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi @Susan Vieira

    Excellent!

    Glad to hear that it was helpful!

    Be safe and have a fantastic day!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Help! Why isn't this working for me?

  • Hi @Kristy McUmber

    I've checked in with our Support team and there is currently a known issue for this exact type of formula if you are evaluating criteria that begins with a 0 or a leading ' apostrophe, as the data in your UPC Code column is formatted.

    To fix the formula in the meantime, add an = sign before your [UPC Code]@row criteria, like this:

    =IF(COUNTIF([UPC Code]:[UPC Code], =[UPC Code]@row) > 1, 1)

    Let me know if this works for you!

    Cheers,

    Genevieve

  • Yes! Thank you! That works!

Sign In or Register to comment.