Identifying Duplicates within a column

Eddie Abasta

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

Bob Andrews

There isnt a built in way, but here's a workaround:

 

-Add a checkbox column to your sheet with a formula that will check the box if the corresponding cell appears more than once in the column

 

=IF(COUNTIF(Cell:Cell, Cell5) > 1, 1)

 

Cell is the column name and this is on row 5

 

-Hide checkbox column

-Enable conditional formatting rule that highlights the cell containing the duplicate if the corresponding checkbox is checked

Bob Andrews

There isnt a built in way, but here's a workaround:

 

-Add a checkbox column to your sheet with a formula that will check the box if the corresponding cell appears more than once in the column

 

=IF(COUNTIF(Cell:Cell, Cell5) > 1, 1)

 

Cell is the column name and this is on row 5

 

-Hide checkbox column

-Enable conditional formatting rule that highlights the cell containing the duplicate if the corresponding checkbox is checked

J. Craig Williams

Bob,

 

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

 

 

I'll use this!

 

Craig

 

 

Eddie Abasta

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!

Matt Wiese

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

J. Craig Williams

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

RichKni

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

 

J. Craig Williams

Like this?

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

Craig

RichKni

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))

marie mcmahon

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

Andrée Starå

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

unbeatable_trading

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.

Please help,

Thanks

Mit

Andrée Starå

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

Michele @ My School DC

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

Lisa Masters

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?

SummerEdwards

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...