Help with formula containing text

[mlhynes]

Hi

I need some help on the following.

I have a column of text, linked to other worksheets, that is
continuously changing. I need to be alert if the same piece of text
appears in the column more than twice, e.g.

Tom
Tom
Jane
Mary
Mary
Tom

"ALERT"

Thanks
Mike

 

[Gord Dibben]

Mike

Visit Chip Pearson's site for much help on duplicates.

Finding, tagging, preventing and others.

http://www.cpearson.com/excel/topic.htm

Scroll down to Duplicates section.


Gord Dibben Excel MVP

 

[Earl Kiosterud]

ml,

The following formula, for rows 2-6 in this example, put into row 2 and
copied down, will yield "DUP" in any duplicate cells in A2:A6.
Unfortunately, it will respond to empty cells as dups, but we'll deal with
that later:
=IF(COUNTIF($A$2:$A$6,A2)>1,"DUP","")

If the worksheet is tall (many rows) and you don't want to have to scan
vertically for the DUP flags, you could use this in a single cell, to
indicate that there are "DUP" flags somewhere in the formulas above. This
is for where the formula above is in C2:C6. It's an array formula (use
Ctrl-Shift-Enter instead of Enter when you're done editing it).
=IF(OR(C2:C6="DUP"),"DUP","")

Or if you don't want the individual DUP flags (first formula), but want a
single cell to signal "DUP" if there are dups anywhere in A2:A6, use:
=OR(COUNTIF(A2:A6,A2:A6)>1)