How to find two cells with matching text

[noblehill]

Hello!

I really suck in Excel formulas and functions, so I need help.

I need to find out if any cells in a column have the exact same text. For example, if cells have text

meir
mair
isir
risr
mair

I need the function to highlight the cells containing "mair". Any ideas?

 

[noblehill]

I forgot to mention that I can't simply use find, because I don't know what I'm looking for, I need to find all duplicates.

 

[xlbo]

use COUNTIF

If your data is in A1:A100 then

=COUNTIF($A$1:$A$100,A1)

is the formula to use. Copy down and then anything with > 1 in the result is a duplicate which you can filter for.

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AnotherHiggins]

-> I need the function to highlight the cells containing "mair". Any ideas?

Let's say these are in column A.

*Highlight column A
*Go to Format > Conditional Formatting.
*Change the first box to Formula Is
*In the second box, type in
[tab][COLOR=blue white]=COUNTIF(A:A,A1)>1[/color]
*Click on Format
*Go to the Patterns tab
*Pick a color for the highlighting
*Click OK
*Click OK


Any dupes will be highlighted

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[noblehill]

Thank you, both! Problem solved.