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)
Best Answers
-
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.
-
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
-
Are you able to provide a screenshot that shows what you are trying to accomplish?
-
Hmm. vlookup to check cell in every column against every other column, and return the duplicate value?
-
@James Keuning Are you able to expand on that?
-
@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.
-
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?
-
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.
-
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 Awesome thank you. Is there a exclude blanks for those formulas?
-
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) + ..................................................................))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!