highlight dups in one of two columns
 

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.

--
Alan

highlight dups in one of two columns
 

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.

Biff

"adgorn" wrote in message
...
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.

--
Alan

highlight dups in one of two columns
 

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?
--
Alan


"Biff" wrote:

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.

Biff

"adgorn" wrote in message
...
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.

--
Alan

highlight dups in one of two columns
 

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?

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

"adgorn" wrote in message
...
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?
--
Alan


"Biff" wrote:

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.

Biff

"adgorn" wrote in message
...
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.

--
Alan