Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Possible Duplicate:
Excel 2010 conditional formatting: selectively highlighting duplicates

First of all, this is basically a cut-and-paste of another question regarding Excel 2010. When I commented on that post, it wouldn't post, so I am trying a whole new question thread.

I'm using Excel 2007 and would like to know if there's any way I can choose conditional formatting to highlight only instances of duplicates when it's found in ALL the columns I chose?

For instance, I have six columns of data, with duplicates in them, but I only want those duplicates to be highlighted if they appear in ALL SIX columns.


A        B        C        D        E        F
Dog      Cat      Bird     Platypus Otter    Lion
Cat      Platypus Zebra    Bird     Lion     Ostrich
Fish     Panda    Giraffe  Dog      Platypus Platypus
Horse    Chicken  Platypus Zebra    Giraffe  Dog
Platypus Dog      Panda    Horse    Zebra    Snake

Only "Platypus" appears in all six columns, but "Dog", "Cat", "Horse", etc all have one or more duplicates, which will usually end up being highlighted. If I can find a solution that will allow me to have the flexibility of choosing to highlight instances of duplicates in 5 out of 6 columns, 4 out of 6 columns, 3 out of 6 columns, etc, that'd be even better!

Note that the data is not arranged nicely in a row, so that I couldn't use "Countif" across rows to see how many times "dog" appeared in Column A-L on a specific row (i.e. "dog" could appear anywhere in the columns, not necessarily on the same row).

I tried using a =AND(COUNTIF($A$1:$A$6,A>1,.........) scheme, but it tells me that I cannot use "unions, intersections, or arrays constants for Conditional Formatting criteria."

If anyone has any tips on this, I'd really appreciate it, thanks!!

share|improve this question

marked as duplicate by studiohack Aug 15 '12 at 1:10

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1 Answer 1

One approach would be a NESTED IF and COUNT. In fact you do not even have to provide the number of columns, as long as there is a non-blank value, you can go through several IF NOT ELSE logic control to highlight 5, 4, 3, 2, columns and their values.

If you ask this question on http://www.mrexcel.com/forum/excel-questions/ , you might get a working script, as there people only devote time to Excel/VBS.

Hope this helps you.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.