how to stop the repeat names

[ancherilvm]

Hello, I have attached a spread sheet with 3 columns names.
Column-1, nurse clinician name
Column-2, Peer-1
Column-3, Peer-2
my question is how to stop or alert for inserting name on Column-3 if the names is same as on Column-2
eg: peer-1 choose Mary Mathew and peer-2 choose Mary Mathew. (so peer-2 should get the alert or color change or some kind of notification.
please help...

[samba_ravi]

you want the alerts when names are in the same row or in when they are in different rows also.

[ancherilvm]

yes, I want the alert for same names are in the row

[63falcondude]

yes, I want the alert for same names are in the row

In that case, disregard the suggestion from post #4.

[ancherilvm]

see the attached book. but there is a problem how can I avoid blank cells. if there is no names. see attached spread sheet
I used this formula =$E4 = $H4

[popipipo]

Delete merge cells

Change datavaldation formula in

=COUNTIF($C4:D4,D4)<2

[63falcondude]

Here's one way through Conditional Formatting (color change):

Highlight H4:H10 > Conditional Formatting > New Rule > Use a formula

=COUNTIF(E:E,H4)

Format: However you'd like. > OK > OK

[mehmetcik]

A conditional format will do that for you

Select A4:J10

Select conditional formatting, New Rule, use formula

enter this in to the formula box

=$E4 = $H4

Select format, select fill, select red.

[samba_ravi]

=AND($E4=$H4,$E4<>"")
use above formula in conditional format

[ancherilvm]

Thanks, this will help

[popipipo]

With datavalidation you can't fill in the same name twice.
Then you don't need conditional format

[ancherilvm]

to popipipo: I had a data validation inserted for to stop same entry more than 2 in column-3. the new validation removes the original. is there anyway I can insert two data validation in the same column?

[ancherilvm]

is there anyway to put together these formula for Column 2 and 3 in the work book
=AND($E4=$H4,$E4<>"")
=IF(COUNTIF($E$1:E4,E4)<3,1,0)

[63falcondude]

Combining those two formulas would give you this:

=AND($E4=$H4,$E4<>"",COUNTIF($E$1:E4,E4)<3)

Untested in Data Validation.

[ancherilvm]

not working

[63falcondude]

That's because one is for Conditional Formatting and the other is for Data Validation.

Try this. Highlight H4:H10 > Data > Data Validation > Allow: Custom > Formula:

=AND(E4<>H4,COUNTIF(E$4:E4,E4)<3)

[ancherilvm]

this formula doesn't stop more than 2 same name entry, but it only restrict the same name entry.

[63falcondude]

Whoops. The formula from post #16 should have been:

=AND(E4<>H4,COUNTIF(H$4:H4,H4)<3)

[ancherilvm]

this works perfect. thanks for all the help

[63falcondude]

You're welcome. Glad we could help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.