Mark Duplicate value

odedL
odedL ✭✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

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

«1

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 EXPERT CONSULTANT & PARTNER

    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.

  • odedL
    odedL ✭✭✭✭✭✭

    many thanks

    it's worked as expected ! 100% !

     

    thanks !

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

    Great!

    Happy to help!

    Best,

    Andrée

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    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.

  • Nicole Hodges
    Nicole Hodges ✭✭✭✭✭✭

    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 EXPERT CONSULTANT & PARTNER

    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.

  • Christopher Pretty
    Christopher Pretty ✭✭✭✭✭✭

    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 EXPERT CONSULTANT & PARTNER

    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

  • Genevieve P.
    Genevieve P. Employee Admin

    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 EXPERT CONSULTANT & PARTNER

    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.

  • Susan Vieira
    Susan Vieira ✭✭✭✭✭

    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 EXPERT CONSULTANT & PARTNER

    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.

  • Kristy McUmber
    Kristy McUmber ✭✭✭✭✭✭

    Help! Why isn't this working for me?

  • Genevieve P.
    Genevieve P. Employee Admin

    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

  • Kristy McUmber
    Kristy McUmber ✭✭✭✭✭✭

    Yes! Thank you! That works!