# Repeat Items

#### miroj1

##### Board Regular
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

##### MrExcel MVP
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

##### Board Regular
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

##### Board Regular
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

##### Board Regular
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?

##### MrExcel MVP
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

##### Board Regular
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

Replies
1
Views
137
Replies
8
Views
310
Replies
3
Views
169
Replies
13
Views
730
Replies
3
Views
336

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.

### Which adblocker are you using?

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

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