Duplicate Formula Not Working As Expected

Mark Strecker

Hello,

I have two columns, one has job numbers in it, and the one beside it has this formula to detect if the first coiumn has duplicates: =IF(COUNTIF([Complete Job Number]:[Complete Job Number], [Complete Job Number]51) > 1, 1).

The row number--51 in my example formula--changes for each row.

It doesn't turn on when I have duplicates, but in the empty cells (you only see one in the screenshot but there are more below it), it does come on. What am I missing here?

Thanks for the help. I appreciate it.

raltamirano

Try

=IF([Complete Job Number]@row = "", 0, IF(COUNTIF([Complete Job Number]:[Complete Job Number], [Complete Job Number]@row) > 1, 1, 0))

This way, if the cell is blank, the box will automatically be checked and if it is not blank, then it will process the equation. Let me know if you have any questions!

Mark Strecker

Well, that got rid of the false positives for blank cells, but the checkboxes still aren't coming on when there is a duplicate number in the "Complete Job Number" column.

Thanks.