Conditional Format ... Compare Columns?

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

Select column B and add this formula in CF

=COUNTIF(D:D,B1)=0

--

HTH

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


"Ken" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

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.


"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
>
>
>

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.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Ken" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

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:D,B1)=0)*ISTEXT(B1)
Choose color from Patterns

--
David

David wrote

> 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:D,B1)=0)*ISTEXT(B1)
> Choose color from Patterns
>

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

--
David