Highlight duplicates across multiple columns

I think I already have the formula working for my Number column, but I would also like it to check additional columns "Number Field 2" "Item Number" for duplicate values.

Not just checking for duplicates down one single column but occurring in any of the fields and highlighting those occurrences.

I hope that explains what I am trying to do.

=AND(COUNTIF(Number:Number, Number@row) > 1, LEN(Number@row) > 0)

Tags:

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    OK. I am going to suggest 3 checkbox helper columns. One for each that you are wanting to evaluate.

    [Number Check] is going to be:

    =IF(COUNTIFS(Number:Number, Number@row) + COUNTIFS(Other:Other, Number@row) + COUNTIFS([Number Filed 2]:[Number Field 2], Number@row) > 1, 1)


    [Other Check] is going to be:

    =IF(COUNTIFS(Number:Number, Other@row) + COUNTIFS(Other:Other, Other@row) + COUNTIFS([Number Filed 2]:[Number Field 2], Other@row) > 1, 1)


    [Number Field 2 Check] is going to be:

    =IF(COUNTIFS(Number:Number, [Number Field 2]@row) + COUNTIFS(Other:Other, [Number Field 2]@row) + COUNTIFS([Number Filed 2]:[Number Field 2], [Number Field 2]@row) > 1, 1)


    Then you will set up 3 separate Conditional Formatting Rules that basically say that if the Check column is checked, highlight the regular column. So for Example you would say that if the [Number Check] column is checked, then highlight the Number column. Duplicate that and update it for the [Other Check] to be checked then highlight the Other column and the same for the [Number Field 2 Check] column.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    I would suggest using an IF statement that looks at the column being evaluated.

    =IF(Number@ row <> "", IF(COUNTIFS(Number:Number, Number@row) + ..................................................................))


    =IF(Other@ row <> "", IF(COUNTIFS(Number:Number, Other@row) + ..................................................................))


    =IF([Number Field 2]@ row <> "", IF(COUNTIFS(Number:Number, [Number Field 2]@row) + ..................................................................))

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!