Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 was marked as an exact duplicate of an existing question.

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