Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I create and save a formula for text fields? | Excel Discussion (Misc queries) | |||
Formulas dealing with text data | Excel Worksheet Functions | |||
Using the text from a cell as a range name in a formula | Excel Discussion (Misc queries) | |||
Coverting Formula to Text | Excel Discussion (Misc queries) | |||
Concatenation formula loses text wrap formatting | Excel Discussion (Misc queries) |