Ignoring Blanks when Identifying Duplicates

andreachadis

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.

andreachadis

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", "")