Repeat Items

miroj1

Board Regular
Joined
May 6, 2002
Messages
52
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.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,229
Messages
6,170,880
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top