How to Check Duplicate numbers within 2 columns of data ?
Best Answer
-
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"))
Answers
-
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.
-
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) ?
-
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"))
-
This worked Great!! Thank you Mr. Chris!!! You're a lifesaver!
-
Happy this worked for you!
-
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?
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!