Checkbox find duplicate in a column, do not check either box if either has a value in 2nd column

Dan Thomas

I am using a checkbox to find duplicate entries in the column name Email. I can up with this that worked, =IF(COUNTIF(Email:Email, Email@row) > 1, 1). 

Next, I am looking for both duplicates to not have a value in the Using Generic Account field and then check both boxes. Do not check either box if one of the two duplicates has a value in the Using Generic Account field. I can up with this =IFERROR(AND(IF(COUNTIF(Email:Email, Email@row) > 1, 1), IF(COUNTIF([Using Generic Account]:[Using Generic Account], [Using Generic Account]@row) > 1, 1)), 0). 

This works if both rows do not have a value in the Using Generic Account field. But if one does, the one without the value is still checked. 

Paul Newcome

I want to make sure I understand...

You have two columns. Email and [Using Generic Account]. You want to only flag duplicates that are blank in the [Using Generic Account] for all entries. If there is no duplicate or even one of the duplicates has data in the [Using Generic Account] column then you want to leave the boxes unchecked?

If the above is correct, try this:

=IF(AND(COUNTIFS(Email:Email, @cell = Email@row)> 1, COUNTIFS(Email:Email, @cell = Email@row, [Using Generic Account]:[Using Generic Account], @cell <> "") = 0), 1)

Paul Newcome

I want to make sure I understand...

You have two columns. Email and [Using Generic Account]. You want to only flag duplicates that are blank in the [Using Generic Account] for all entries. If there is no duplicate or even one of the duplicates has data in the [Using Generic Account] column then you want to leave the boxes unchecked?

If the above is correct, try this:

=IF(AND(COUNTIFS(Email:Email, @cell = Email@row)> 1, COUNTIFS(Email:Email, @cell = Email@row, [Using Generic Account]:[Using Generic Account], @cell <> "") = 0), 1)

Dan Thomas

That is correct. Thank you for your help! It worked!

Paul Newcome

Happy to help. 👍️