Nested IF Statement for Flagging Duplicates

ogonzalez

Hi,

I am looking to create a nested IF statement that allows me to look at multiple columns for duplicates. It is only true if there is a duplicate in each of the columns. 

For Example:

First             Last               Job

Jeff              Smith             Programmer

Jeff              Smith             Designer

Jeff              Smith              Designer

 

In this scenario, I want a flag checked off when ALL 3 appear more than once. I am able to do this with a single column but with multiple it is a bit difficult. In this situation a person can have a multiple roles but I want to flag it when 3 criteria are all the same.

I have:

=IF(First:First,First2>1, IF(Last:Last,Last2>1, IF(Job:Job,Job2>1,1,0)))

The "2" is simply the designation of the row I am in so that it compares that row to the entire column. I've been coming up with UNPARSEABLE. Any ideas would be helpful.

Chris McKay

Hi ogonzalez,

Based on the column names you have provided above, you could try this:

1. Create a 4th column and call it Duplicate Flag.
2. Set the column properties to Checkbox.
3. On the 1st row, in the new Duplicate Flag column, enter the following formula:
   
   =IFERROR(IF(LEN(First1) = 0, "", IF(COUNTIFS(First:First, First1, Last:Last, Last1, Job:Job, Job1) = 1, 0, 1)), 0)
4. Drag the cell handle (located bottom right) all the way down the column as far as you want.
5. Right-click the Duplicate flag column header and select Lock Column

The formula will automatically hide the checkbox if there is no value in the First column.

You can edit the formula to include more columns or even limit it to a certain range of rows if you choose. I hope this helps.

Schiff A.

Hello,



I suggest taking a slightly different approach than using a nested IF statement for this.  Here is an alternative solution:

1. Create a column called "String" and enter the formula =First1+Last1+Job1

Drag the formula down to cover the entire range of data. This will create a single string out of the name/job combination (eg. JeffSmithDesigner



2. In the checkbox column, enter the formula =IF(COUNTIF(String:String, =String1) > 1, 1, 0)

Drag the formula down to cover the entire range of data. This will look at the String column and count the number of times each string appears.  If a given string appears more than once (in other words, if there is a duplicate) then the box will be checked.

3. (Optional) Hide the String column.

 

Thanks for using Smartsheet!

J. Craig Williams

Both are excellent answers. 

Questions for Chris:

Why are you checking for length? 

What error are you getting / expecting that causes you to wrap the formula in IFERROR?

I think I get the same results with:

=IF(COUNTIFS(First:First, First1, Last:Last, Last1, Job:Job, Job1) = 1, 0, 1)

Comment for Schiff:

You don't need to put the equal sign in the criterion (=String1). The equals is understood. I avoid adding things that may confuse the parser.

Further general comments:

If the columns are sequential (and I suspect they are), I would use JOIN() instead of Schiff's concatenation.

=JOIN(First1:Job1, " ")

This results in a human readable result (Jeff Smith Designer instead of JeffSmithDesigner) which might be used elsewhere.

This also differentiated Jeff Smith Designer from Jef fSmit hDesigner. I doubt there are many cases where this is a problem, and I would not change it for this reason, only pointing it out.

I would tend for Schiff's method if cell numbers and formula count was not too high or if I needed/wanted the combined string for use elsewhere. Otherwise, Chris' solution (or mine without the error check and length) would be preferred.

Craig

Chris McKay

Hi Craig,

IFERROR is there out of habit. I find it eliminates unwanted values being displayed in more complex formulas. It can probably be dropped from this example.

The inclusion of LEN is simply to hide the checkbox in the event the row contains no data (so it looks neater) 

J. Craig Williams

Chris,

Makes sense.

I avoid IFERROR unless I have specific error(s) I am expecting. I don't want to go digging for a masked problem. If I don't know what the errors might be, I return "error" instead of a valid value.

Craig

MATRIX Architect

Chris, your solution worked for me. I am now trying to do the same for a column called "Serial Number" to find duplicates. Can you help me with a formula for that?

Duplicates     Serial Number

  ____               789456123

  ____               789456123

MATRIX Architect

I played with it and figured it out...

=IFERROR(IF(LEN([Serial Number]1) = 0, "", IF(COUNTIFS([Serial Number]:[Serial Number], [Serial Number]1) = 1, 0, 1)), 0)

Marilen.Navarro103391

Hi Matrix,

I followed this serial number duplicates. The formula works for me. But I need to create a report to show the serial numbers which is not duplicated. Can you show me howto show this in report. The serial numbers must be unique. Thanks in advance.

Andrée Starå

Hi Marilen,

Did you get it working or do you still need help?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Marilen.Navarro103391

I'm good. Thanks.

Andrée Starå

Excellent!

I'm always happy to help!