Help with formula containing text
 

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


--
mlhynes
------------------------------------------------------------------------
mlhynes's Profile: http://www.excelforum.com/member.php...o&userid=12959
View this thread: http://www.excelforum.com/showthread...hreadid=401787

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

On Sun, 4 Sep 2005 09:49:11 -0500, mlhynes
wrote:


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

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)
--
Earl Kiosterud
www.smokeylake.com

"mlhynes" wrote in
message ...

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


--
mlhynes
------------------------------------------------------------------------
mlhynes's Profile:
http://www.excelforum.com/member.php...o&userid=12959
View this thread: http://www.excelforum.com/showthread...hreadid=401787