# Identifying Duplicates within a column

edited 12/01/22

Does anyone know how i can do this?  it seems that SmartSheet does not have the ability to do this as in Excel.

Tags:

• ✭✭✭✭✭✭

Bob,

Pretty clever. I did not get it at first, but implementation solved that.

I'll use this!

Craig

• That worked nicely!  I had to add an additional column to the side so it is not visible and then used this formula, finally I entered conditional formatting to the column where i want the notification of duplicates.

This may be something that SmartSheet may consider as an add, so that the functionality exists as it does in Excel.  Thanks Bob!

• edited 05/11/18

Just replying (very late) to say this is awesome! Thanks Bob. Have you been able to apply this method between two sheets?

• ✭✭✭✭✭✭

It will work across sheets. Before X-Sheet References implementation, I used JOIN() and FIND() to flag the non-duplicates. In limited cases, I still will.

Craig

• edited 06/27/18

Can you provide an expression that does not evaluate a blank cell?

I tried this but I get an UNPARSEABLE error

=IF([Serial Number]:[Serial Number]=" "," ",(COUNTIF([Serial Number]:[Serial Number], [Serial Number]5) > 1, 1))

=IF(COUNTIF[Serial Number]:[Serial Number]< 1, 0,COUNTIF([Serial Number]:[Serial Number], [Serial Number]5) > 1, 1)

and other variations... Blasted Excel logic

• ✭✭✭✭✭✭

Like this?

=IF(COUNTIFS([Serial Number]:[Serial Number], [Serial Number]@row, [Serial Number]:[Serial Number], NOT(ISBLANK(@cell))) > 1, 1)

Craig

• Thank you for your reply, however I got this one to work!

On my sheet: =IF([Serial Number]5 = 0, 0, IF(COUNTIF([Serial Number]:[Serial Number], [Serial Number]5) > 1, 1))

Generic sheets: =IF([Column]5 = 0, 0, IF(COUNTIF([Column]:[Column], [Column]5) > 1, 1))

• Hi

Im sorry im not getting exactly how to do this , i think i am close , but just not there yet !

please can you write step by step ?

I have a sheet that has task name ( this is column i want to check for duplicates and flag the cell in red colour) I made a rule in conditional formatting :

If Task name , contains =IF(COUNTIF(Cell:Cell, Cell5) > 1, 1) apply red format to task name column

appreciate any help

• ✭✭✭✭✭✭

Hi Marie,

Did you get it working or do you need help?

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? That would make it easier to help.

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 for help with Smartsheet, integrations, general workflow advice, or anything else.

• I'm also trying to get this to work and I can't.

I have a "Serial Number" column and I'm trying to make sure that when a user scan/enter a serial number in any of the cells I want to identify it as a duplicate.

Thanks

Mit

• ✭✭✭✭✭✭

Hi Mit,

Did you try the methods above in this thread?

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? That would make it easier to help.

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 for help with Smartsheet, integrations, general workflow advice, or anything else.

• Thanks Bob and Craig, this just saved me a ton of trouble.

• Is there a way to also reference a second column in checking for the duplicate?  For example, I want the sheet to check for duplicates in the "Invitee Name" column, but only if the "Event Name" is also the same.  Is this possible?

• Is there a way to do this but have the column generate a new number for each set of duplicates.  For example if I have a column of ID numbers that contain duplicates for each ID Number, how can I assign 1's to all of the 555 ID numbers then 2s for all 777 ID numbers, etc...

This discussion has been closed.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!