Highlight duplicates across multiple columns

Kevin Harris · 03/18/21

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)

Paul Newcome · 03/22/21

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 · 03/23/21

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

Paul Newcome · 03/19/21

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

James Keuning · 03/21/21

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

Paul Newcome · 03/22/21

@James Keuning Are you able to expand on that?

Kevin Harris · 03/22/21

@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 · 03/22/21

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?

Kevin Harris · 03/22/21

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 · 03/22/21