Help identifying duplicates using two columns but excluding entries.

MaryMc

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!

AravindGP

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.

AravindGP

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.

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.