How to Check Duplicate numbers within 2 columns of data ?

JJK_Busing
JJK_Busing ✭
edited 02/12/24 in Formulas and Functions

Lets say I have column A and Column B

image.png

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

· Share on FacebookShare on Twitter

Best Answer

Answers

  • =Chris Palmer
    =Chris Palmer ✭✭✭✭✭

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

    image.png

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

    image.png


    https://www.linkedin.com/in/zchrispalmer/

    · Share on FacebookShare on Twitter
  • JJK_Busing
    JJK_Busing ✭
    edited 02/12/24

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

    image.png

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

    · Share on FacebookShare on Twitter
  • =Chris Palmer
    =Chris Palmer ✭✭✭✭✭
    Answer ✓
    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"))

    https://www.linkedin.com/in/zchrispalmer/

    · Share on FacebookShare on Twitter
  • JJK_Busing
    JJK_Busing ✭

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

    · Share on FacebookShare on Twitter
  • =Chris Palmer
    =Chris Palmer ✭✭✭✭✭
    https://community.smartsheet.com/discussion/comment/419399#Comment_419399

    Happy this worked for you!

    https://www.linkedin.com/in/zchrispalmer/

    · Share on FacebookShare on Twitter
  • JJK_Busing
    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?

    image.png


    · Share on FacebookShare on Twitter
  • =Chris Palmer
    =Chris Palmer ✭✭✭✭✭
    edited 02/23/24
    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.

    https://www.linkedin.com/in/zchrispalmer/

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!