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!!