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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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

miroj1

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

johny

Board Regular
Joined
Jul 15, 2002
Messages
69
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

miroj1

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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

johny

Board Regular
Joined
Jul 15, 2002
Messages
69
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,195,595
Messages
6,010,632
Members
441,558
Latest member
lambierules

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
Top