Conditional Format ... Compare Columns?

[Ken]

Excel 2000 ...

Col B contains 1000 alpha-numerics (many repeat)

Col D contains 50 alpha-numerics (none repeat)

I would like "Conditional Formatting" Formula in Col B to
high-lite all alpha-numerics not found in Col D.

Thanks ... Kha

 

[Bob Phillips]

Select column B and add this formula in CF

=COUNTIF(D,B1)=0

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Earl Kiosterud]

I can't think of a way do do this without an array formula, and I can't
think of a way to put an array formula in conditional formatting. Maybe I
just can't think at all! Here's a formula to put in a helper column, which
can then be tested with conditional formatting:
=IF(AND(B1<>$D$1:$D$3),"*","")

It's a array formula - use Ctrl-Shift-Enter. Enter it into a single cell
(it's a single-result array formula), then copy down with the Fill Handle.
You can hide this column.

Now set up conditional formatting to highlight the row if there's an
asterisk in the column. If it's in column F, you could use:
Format - Conditional formatting - Formula is:
=$F2="*"
This is for where the active (white) cell of your selection is in row 2.

I don't know why I'm doing this. SOmeone is probably posting a way to do it
directly in Conditional Formatting right now.

 

[Guest]

Hi Ken,

Select Col B and do Format>conditional formatting.
On the Conditional Formatting screen choose 'Formula is' and enter
=countif(B1:B50,D1)<1 then just choose your highlighting color.

Hope that helps.

 

[David]

Ken wrote

Excel 2000 ...

Col B contains 1000 alpha-numerics (many repeat)

Col D contains 50 alpha-numerics (none repeat)

I would like "Conditional Formatting" Formula in Col B to
high-lite all alpha-numerics not found in Col D.

Thanks ... Kha

If I understand what you mean by alph-numerics, how about:
Select Col B
Format|Conditional Formatting|Formula Is
=(COUNTIF(D,B1)=0)*ISTEXT(B1)
Choose color from Patterns

 

[David]

David wrote

Ken wrote


If I understand what you mean by alph-numerics, how about:
Select Col B
Format|Conditional Formatting|Formula Is
=(COUNTIF(D,B1)=0)*ISTEXT(B1)
Choose color from Patterns

Or this (simpler?):
=AND(B1<>"",COUNTIF(D,B1)=0)