IF formula returns unexpected TRUE result for text cells that start with the < or > character

Liesik

I have text column in one of my sheets named EN Display Name. That column cannot contain duplicate values, so I added the formula below to a helper column in the sheet to make it more obvious when multiple rows had the same value in the EN Display Name column.

=IF(COUNTIF([EN Display Name]:[EN Display Name], [EN Display Name]@row) > 1, IF([EN Display Name]@row <> "", "DUPLICATE", ""), "")

I just added my first instance of an EN Display Name that was wrapped in the <i> </i> tags to the sheet and it got flagged as a duplicate. After a bit of trial and error, I discovered that my helper column formula was treating any value starting with the < character and an immediately adjacent letter as a TRUE result. The same thing happens if the value starts with the > character, either by itself or with any adjacent character.

Here's a screenshot of a separate sheet I created to do my testing.

Is there any way to get the formula in my helper column to treat these characters a plain text when checking for duplicates?

Carson Penticuff

I was able to recreate your testing, so this does appear to be an issue. I was able to put together this somewhat "hackey" workaround.

=IF(COUNTIF([EN Display Name]:[EN Display Name], "'" + @cell = "'" + [EN Display Name]@row) > 1, IF([EN Display Name]@row <> "", "DUPLICATE", ""), "")

It appears the greater than and less than symbols are being ignored when then appear as the first character. This appends an apostrophe before the comparison, so the symbol is no longer the first character. I hope this helps.

Kelly Moore

Hey @Liesik and @Carson Penticuff

I was also intrigued by the anomaly and found that the HAS function seems to work in what I tested

=IF(COUNTIFS([EN Display Name]:[EN Display Name], AND(HAS(@cell, [EN Display Name]@row), @cell <> "")) > 1, "Duplicate")

All of the formulas above will highlight multiple instances of the same Duplicate, including the first entry which is not yet a duplicate. If you wanted to find only the duplicates and leave the first instance untouched, you can do this using a system column like autonumber ([RowID] or Created date - assuming your rows are remaining in chronological order.

Using [Row ID] as an example:

=IF(COUNTIFS([EN Display Name]:[EN Display Name], AND(HAS(@cell, [EN Display Name]@row), @cell <> ""), [Row ID]:[Row ID], @cell<=[Row ID]@row) > 1, "Duplicate")

Kelly

Carson Penticuff

I was able to recreate your testing, so this does appear to be an issue. I was able to put together this somewhat "hackey" workaround.

=IF(COUNTIF([EN Display Name]:[EN Display Name], "'" + @cell = "'" + [EN Display Name]@row) > 1, IF([EN Display Name]@row <> "", "DUPLICATE", ""), "")

It appears the greater than and less than symbols are being ignored when then appear as the first character. This appends an apostrophe before the comparison, so the symbol is no longer the first character. I hope this helps.

Kelly Moore

Hey @Liesik and @Carson Penticuff

I was also intrigued by the anomaly and found that the HAS function seems to work in what I tested

=IF(COUNTIFS([EN Display Name]:[EN Display Name], AND(HAS(@cell, [EN Display Name]@row), @cell <> "")) > 1, "Duplicate")

All of the formulas above will highlight multiple instances of the same Duplicate, including the first entry which is not yet a duplicate. If you wanted to find only the duplicates and leave the first instance untouched, you can do this using a system column like autonumber ([RowID] or Created date - assuming your rows are remaining in chronological order.

Using [Row ID] as an example:

=IF(COUNTIFS([EN Display Name]:[EN Display Name], AND(HAS(@cell, [EN Display Name]@row), @cell <> ""), [Row ID]:[Row ID], @cell<=[Row ID]@row) > 1, "Duplicate")

Kelly

Liesik

@Carson Penticuff / @Kelly Moore, both of your suggestions seem to do the trick, though I ultimately decided to go with Kelly's =IF(COUNTIFS([EN Display Name]:[EN Display Name], AND(HAS(@cell, [EN Display Name]@row), @cell <> "")) > 1, "Duplicate") suggestion. Thanks tons for the quick assist!

Carson Penticuff

Personally, I would have gone with Kelly's as well... but don't tell anyone I said that. 😁