Checkbox find duplicate in a column, do not check either box if either has a value in 2nd column
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.
Best Answer
-
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)
Answers
-
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)
-
That is correct. Thank you for your help! It worked!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!