Ignoring Blanks when Identifying Duplicates
Hello! I've been struggling with this and keep coming up with nothing. Would love some help please!
We have a sheet with sessions for a conference, in which I've added helper columns to identify the duplicates (for speakers that have more than one session at a time). As of right now, everything is coming up as a duplicate in the [Speaker Dupe Flag] column, I think because of the blank cells in the [Speaker 3]-[Speaker 5] columns, I've tried a number of ways to ignore those cells and keep getting various errors.
This is the formula that is correctly identifying duplicates right now in the [Speaker Dupe Flag] column:
=IF(OR(COUNTIF([Speaker 1]:[Speaker 5], [Speaker 1]@row) > 1, COUNTIF([Speaker 1]:[Speaker 2], [Speaker 2]@row) > 1, COUNTIF([Speaker 1]:[Speaker 5], [Speaker 3]@row) > 1, COUNTIF([Speaker 1]:[Speaker 5], [Speaker 4]@row) > 1, COUNTIF([Speaker 1]:[Speaker 5], [Speaker 5]@row) > 1), "Speaker Duplicate", "")
Edited to add that this formula is producing the same result as the screenshot below:
=IF(OR(COUNTIF([Speaker 1]:[Speaker 5], [Speaker 1]@row) > 1, COUNTIF([Speaker 1]:[Speaker 2], [Speaker 2]@row) > 1, COUNTIF([Speaker 1]:[Speaker 5], [Speaker 3]@row) > 1, COUNTIF([Speaker 1]:[Speaker 5], [Speaker 4]@row) > 1, COUNTIF([Speaker 1]:[Speaker 5], [Speaker 5]@row) > 1), IF(OR(NOT(ISBLANK([Speaker 1]@row)), NOT(ISBLANK([Speaker 2]@row)), NOT(ISBLANK([Speaker 3]@row)), NOT(ISBLANK([Speaker 4]@row)), NOT(ISBLANK([Speaker 5]@row))), "Speaker Duplicate"), "")
But how do I get it to ignore the blanks? Any guidance is appreciated.
Answers
-
I figured it out, but can't figure out how to close out my question or delete it!
This worked:
=IF(OR(COUNTIFS([Speaker 1]:[Speaker 5], [Speaker 1]@row, [Speaker 1]:[Speaker 5], <>"") > 1, COUNTIFS([Speaker 1]:[Speaker 5], [Speaker 2]@row, [Speaker 1]:[Speaker 5], <>"") > 1, COUNTIFS([Speaker 1]:[Speaker 5], [Speaker 3]@row, [Speaker 1]:[Speaker 5], <>"") > 1, COUNTIFS([Speaker 1]:[Speaker 5], [Speaker 4]@row, [Speaker 1]:[Speaker 5], <>"") > 1, COUNTIFS([Speaker 1]:[Speaker 5], [Speaker 5]@row, [Speaker 1]:[Speaker 5], <>"") > 1), "Speaker Duplicate", "")
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!