highlight dups in one of two columns

A

adgorn

I have words in column A and B. I'd like to use conditional formating to
highlight just those words in A that also occur in B. The following is
close, but I believe gives a mirror image of what I want. Appreciate any
help. Thanks.

1. Select column B (click the letter B)
2. Choose Format > Conditional formatting
3. In ""Condition 1"" choose ""Formula is"""
4. Enter this formula =(COUNTIF($A:$A,B1)=0)*(B1<>"")
5. Click the ""Formats"" button and take your pick. OK.OK.
 
B

Biff

Try this:

Assume the range of names is in A1:A10 and B1:B10
Select the range A1:A10 (don't select the entire column, it's a waste of
resources to format cells that aren't being used)
2. Choose Format > Conditional formatting
3. In ""Condition 1"" choose ""Formula is"""
4. Enter this formula:
=MATCH(A1,B$1:B$10,0)

5. Click the ""Formats"" button and take your pick. OK.OK.
Click to expand...

Biff
 
A

adgorn

Works perfectly! Thanks for taking the time to help me.

I'd like to learn more about how this function works within conditional
formatting. Is it possible to give a brief explanation?
 
B

Biff

You're welcome. Thanks for the feedback!
I'd like to learn more about how this function works within conditional
formatting. Is it possible to give a brief explanation?
Click to expand...

Sure

=MATCH(A1,B$1:B$10,0)

The lookup value is cell A1. The lookup array is B1:B10. 0 means we want to
find an exact match. If the lookup value is found in the lookup array it
returns a number that is the relative position of the lookup value within
the lookup array:

A1 = green

B1 = black
B2 = green
B3 = red

=MATCH(A1,B1:B3,0) returns 2 because the lookup value green is in the 2nd
position relative to the lookup array.

How this works in conditional formatting:

In order for the format to be applied the condition must evaluate to TRUE. A
formula that returns a number like the MATCH function can be used because
*ANY* number other than 0 will evaluate as TRUE and cause the format to be
applied. A 0 will evaluate to FALSE. An error value like #N/A which the
MATCH formula would return if no match was found would also cause the format
to NOT be applied.

Biff
 
Top