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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • 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) + ..................................................................))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide a screenshot that shows what you are trying to accomplish?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • James Keuning
    James Keuning ✭✭✭✭✭

    Hmm. vlookup to check cell in every column against every other column, and return the duplicate value?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @James Keuning Are you able to expand on that?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • @Paul Newcome here is a screenshot of what i am working with. I would like to see any time a duplicate occurs throughout columns Number, Other, or Number Field 2. The Other column is a multi line text/number field. I hope that helps.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Just to make sure I am clear...


    Using your screenshot, if 1547 is found a second time in the Number column, or has even one occurrence in the Other or [Number Filed 2] columns (since it is already in the Number column), you want it to count as a duplicate?


    Basically you want to flag when that number appears more than one time across all three columns regardless of the row?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Yes I think we are on the same page.

    Another way of explaining my request would be if I were to select those three columns and then CTRL F "1547" then Replace with same text but format fill green. Highlight all/any occurrence.

  • 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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • @Paul Newcome Awesome thank you. Is there a exclude blanks for those formulas?

  • 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) + ..................................................................))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!