Help identifying duplicates using two columns but excluding entries.

MaryMc
MaryMc
edited 03/07/24 in Formulas and Functions

I would like to locate duplicates in my sheet using the name column and email column. Just starting with the name column gets me nowhere. I keep getting #UNPARSEABLE. I am using =IF(COUNTIF(Client Name:Client Name, Client Name@row) > 1, 1) in a separate column.

Eventually I would like to include the email column which has two entries for an automation but I need to exclude one of the email addresses.

What am I doing wrong in the first formula and how do I incorporate the second part?

Thank you!

Best Answer

  • AravindGP
    AravindGP ✭✭✭✭✭
    Answer ✓

    Hi @MaryMc


    Since the field Client Name contains a space, you will need to modify the formula as =IF(COUNTIF([Client Name]:[Client Name], [Client Name]@row)>1, 1). You will see the reference and criteria will change to a color when Smartsheet is able to identify the column name. As for email, is it a contact list column with multiple values enabled? If you can provide a sample, I can try to give you the modified formula.

    Thanks,

    Aravind

    Reach out for any help on licenses, configuration, or training

Answers

  • AravindGP
    AravindGP ✭✭✭✭✭
    Answer ✓

    Hi @MaryMc


    Since the field Client Name contains a space, you will need to modify the formula as =IF(COUNTIF([Client Name]:[Client Name], [Client Name]@row)>1, 1). You will see the reference and criteria will change to a color when Smartsheet is able to identify the column name. As for email, is it a contact list column with multiple values enabled? If you can provide a sample, I can try to give you the modified formula.

    Thanks,

    Aravind

    Reach out for any help on licenses, configuration, or training

  • James Keuning
    James Keuning ✭✭✭✭✭

    Client Name@row should have brackets, is that the problem?

    [Client Name]

    For the second part, make an autonumber column called Row ID, and then use this formula:

    =IF([Row ID]@row = MIN(COLLECT([Row ID]:[Row ID], [Client Name]:[Client Name], [Client Name]@row)), [Client Name]@row)

    This formula collects the Row ID for every Client Name, and if it finds more than one instance, it returns the Row ID for the first one, and then on the row containing that Row ID, it returns the Client Name.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!