Highlighting Duplicates

Alex Ceroni

Hi,

This is very useful.

I am trying to adapt this formula to my entries which are names

=IF(COUNTIFS([name]:[name], [name]@row) > 1, "Red")

Every time a new name is added it will flag it as a red dot if the name is already in the list. However, sometimes the names contain portions of other names, eg QWERT and QWE (these are unique). How do I ensure that QWE is not flagged up as duplication?

I am new to SmartSheet and this may be a very basic question.

Many thanks,

Alex

Paul Newcome

@Alex Ceroni QWE should not be flagged because of a QWERT entry based on your formula provided as the formula should be looking for an exact match.

lawrencena@gru.com

Ok so I have used:

=IF(COUNTIF(Phone MAC Address: Phone MAC Address, Cell1) > 1, 1)

And 

=IF(COUNTIFS(Phone MAC Address:Phone MAC Address, Phone MAC Address@row) >1, 1)

Both are coming up unparseable. Can anyone help in what I should fix to have it work?

Genevieve P.

Hi @lawrencena@gru.com

It looks like you may not be referencing the column names in [these], which could be causing the error. To populate a column name in a formula, try clicking on a cell in that column instead of typing it in.

Your formula should look something more like this:

=IF(COUNTIFS([Phone MAC Address]:[Phone MAC Address], [Phone MAC Address]@row) >1, 1)

This Help Article has more information on referencing columns in formulas: Create a Cell or Column Reference in a Formula

Cheers!

Genevieve

Tyler Garnett

Hello,

I am trying to find duplicates using this formula:

=IF(COUNTIFS([Restaurant #]:[Restaurant #], =[Restaurant #]@row) > 1, 1)

I am using a checkbox but numbers that I know are duplicates are not being checked.

Can someone help with this? I have attached a screen shot.

Thanks in advance!

Capture.PNG

Paul Newcome

@Tyler Garnett Do you have restaurant numbers that don't have the leading zero? If so, you have some that are number values and some that are text values. All values must be the same type. You are going to want to add a helper column and use the column formula of...

=[Restaurant #]@row + ""

This converts everything to a text value. Now you can reference this helper column in your COUNTIFS.

Tyler Garnett

@Paul Newcome most of the restaurant number start with zero but there are some that start with seven.

I tried this formula but i got an invalid operation error

=IF(COUNTIFS([Restaurant #]:[Restaurant #], =[Restaurant #]@row + "") > 1, 1)

Is this right?

Paul Newcome

No. You will need an additional column with the

=[Restaurant #]@row + ""

in it. Then shift your COUNTIFS range to look at this new column.

Leslie Murray

Hi there - Is it possible to flag if either of two conditions are met?

We are collecting RSVPs and I want to know if a submission has the same ID or if they have the same Email, but not if both. I have my helper column created for duplicates and I feel like I'm close based on this thread but can't get it to function correctly..... Here's where I am at:

=IF(OR(COUNTIFS(Email:Email, Email@row) > 1, COUNTIFS(ID:ID, ID@row) > 1, 1))

Like the RSVP may sometimes have two of the same IDs or the RSVP may have the same of the email, but rarely will it be a complete duplicate with the same ID and same Email.

Thanks!!

Genevieve P.

Hi @Leslie Murray

You are very close! The formula you have is correct, however you're missing the closing parentheses for the OR statement that should go after the second COUNTIFS but before the final ,1 for the IF statement.

Try this:

=IF(OR(COUNTIFS(Email:Email, Email@row) > 1, COUNTIFS(ID:ID, ID@row) > 1), 1)

This will check the box if either the Email or the ID is the same. However keep in mind if it is an exact duplicate, with both the same email and same ID as another row, then this will still check the box. Is this what you were looking to do?

Cheers,

Genevieve

Leslie Murray

Hi @Genevieve P - Amazing, thank you!!

Zest

Hoping someone can help 😊

Have set a formula as follows to flag any duplicate SKU's, =IF(COUNTIFS([Vend SKU]:[Vend SKU], [Vend SKU]@row) > 1, 1) however the empty cells in the SKU column are also being ticked as they are all duplicate. How do I add to the formula so any empty cells don't get ticked just duplicate values?

Genevieve P.

https://community.smartsheet.com/discussion/comment/326150#Comment_326150

Hi @Zest

You can add an IF statement that says IF the cell is blank, return 0, otherwise go through the formula.

Try this:

=IF([Vend SKU]@row = "", 0, IF(COUNTIFS([Vend SKU]:[Vend SKU], [Vend SKU]@row) > 1, 1))

Cheers,

Genevieve