Repeat Items

[miroj1]

Hello again Excel Masters. Here is my question for the day:
Let us say I have 3 columns of data:
Column1:
Cat
Dog
Bird
Cat

Column2:
Horse
Dog
Monkey
Cat

Column3:
Bird
Dog
Monkey
Cat

How can I highlight or mark the cells that have repeat items. (e.g. All cells with 'Cat' would have a grey background, all cells with 'Dog' would have a green background.)
Using this example is the best way to describe my problem.
Thanks for the assistance.

 

[Aladin Akyurek]

Let A2:C5 house the sample you provided.

In D2 enter & copy down:

=IF((COUNTIF($B$2:$B$5,A2)+COUNTIF($C$2:$C$5,A2))>=2,IF(COUNTIF($D$1:D1,A2),"",A2),"")

Is this what you're looking for?

 

[miroj1]

Thanks, but I wasn't looking for that result.
Any cell that has that has an identical value with another cell needs to highlighted or marked. (E.G. Every cell that has 'Cat' in it needs to be shaded or distinguished differently from the other items on the list because somewhere else on the list another cell has the word 'Cat' in it.)
I hope I'm clear enough.
Thanks for your help on this.

 

[johny]

if a name is entered more then once in a range
you want it to Highlight a color.
use this in conditional formatting
=COUNTIF($A$11:$A$94,$A19)>1
this has to go in "Formula is" not cell "value is" then choose the format you
want for patterns. In the formula above it
will check the range you speicify A11 to a94
it what I have. Thanks Johnny

 

[miroj1]

Sorry, but when I entered your formula, it reformatted everything on the list and gave it a new color. I enetered...=countif($a$2:$c$5,$a2)>1. Did I do something wrong?

 

[Aladin Akyurek]

On 2002-08-28 14:48, miroj1 wrote:
Thanks, but I wasn't looking for that result.
Any cell that has that has an identical value with another cell needs to highlighted or marked. (E.G. Every cell that has 'Cat' in it needs to be shaded or distinguished differently from the other items on the list because somewhere else on the list another cell has the word 'Cat' in it.)
I hope I'm clear enough.
Thanks for your help on this.

No. If you're thinking to color every common element to the three columns distinctly using conditional formatting, you might run out of colors. Conditional formatting has a limit of 3. What happens when you have 100 common elements! That's why I did not suggest conditional formatting.

 

[johny]

Actually Aladin's example is a good example
Sorry. Try it out its not color coded but
it works if three name are the same in all three columns.
Delete the conditional formatting if you haven't
already. thanks John