How to Check Duplicate numbers within 2 columns of data ?

edited 02/12/24 in Formulas and Functions

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 ?

Best Answer


  • Mr. Chris
    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
    edited 02/12/24

    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
    Mr. Chris ✭✭✭✭✭
    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"))

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

  • 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
    Mr. Chris ✭✭✭✭✭
    edited 02/23/24

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!