How to Check Duplicate numbers within 2 columns of data ?

JJK_Busing

Lets say I have column A and Column B

I want to be able to highlight the 123's because they are duped between the 2 columns. How can I do this ?

Mr. Chris

https://community.smartsheet.com/discussion/comment/419320#Comment_419320

You're welcome. This should do the trick:

=IF(OR(COUNTIF([Column 1]:[Column 1], [Column 2]@row) > 0, COUNTIF([Column 2]:[Column 2], [Column 1]@row) > 0), "Dupe", IF(OR(COUNTIF([Column 1]:[Column 1], [Column 1]@row) > 1, COUNTIF([Column 2]:[Column 2], [Column 2]@row) > 1), "Dupe", "No Dupe"))

Mr. Chris

One way to go about this is to create a column and write a formula that checks for the duplicates.

=IF(OR(COUNTIF([Column 1]:[Column 1], [Column 2]@row) > 0, COUNTIF([Column 2]:[Column 2], [Column 1]@row) > 0), "Duplicate", "No Duplicate")

You can set a conditional format to highlight the cells that say "Duplicate" to make them easy to spot visually.

JJK_Busing

Thank you so much @Mr. Chris. The yellow dups were caught...One more small addition...

Can there be an addition to the formula to catch the blue (in the same column too) ?

Mr. Chris

https://community.smartsheet.com/discussion/comment/419320#Comment_419320

You're welcome. This should do the trick:

=IF(OR(COUNTIF([Column 1]:[Column 1], [Column 2]@row) > 0, COUNTIF([Column 2]:[Column 2], [Column 1]@row) > 0), "Dupe", IF(OR(COUNTIF([Column 1]:[Column 1], [Column 1]@row) > 1, COUNTIF([Column 2]:[Column 2], [Column 2]@row) > 1), "Dupe", "No Dupe"))

JJK_Busing

This worked Great!! Thank you Mr. Chris!!! You're a lifesaver!

Mr. Chris

https://community.smartsheet.com/discussion/comment/419399#Comment_419399

Happy this worked for you!

JJK_Busing

Mr. Chris, I ran into 1 snafu, not sure if you how to resolve for this.

The formula fails to work if it has a leading zero. Can the numbers with a leading '0' also be caught ? Thoughts?

Mr. Chris

https://community.smartsheet.com/discussion/comment/420500#Comment_420500

Perhaps this may work by adding VALUE to the formula:

=IF(OR(COUNTIF([Column 1]:[Column 1], VALUE([Column 2]@row)) > 0, COUNTIF([Column 2]:[Column 2], VALUE([Column 1]@row)) > 0), "Dupe", IF(OR(COUNTIF([Column 1]:[Column 1], VALUE([Column 1]@row)) > 1, COUNTIF([Column 2]:[Column 2], VALUE([Column 2]@row)) > 1), "Dupe", "No Dupe"))

If this doesn't work you could resort to adding two additional helper columns that are called:

"Value Column 1" with formula =VALUE([Column 1]@row)

"Value Column 2" with formula =VALUE([Column 2]@row)

Then update the initial semi working formula to the VALUE Columns

=IF(OR(COUNTIF([Value Column 1]:[Value Column 1], [Value Column 2]@row) > 0, COUNTIF([Value Column 2]:[Value Column 2], [Value Column 1]@row) > 0), "Dupe", IF(OR(COUNTIF([Value Column 1]:[Value Column 1], [Value Column 1]@row) > 1, COUNTIF([Value Column 2]:[Value Column 2], [Value Column 2]@row) > 1), "Dupe", "No Dupe"))

It's not pretty, but could be a workaround since the leading 0 converts the numbers to text which causes issues with formulas determining numbers from text, which is likely the root cause of the issue you're experiencing.