Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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" wrote in message ... 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 |
#3
|
|||
|
|||
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" wrote in message ... 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 |
#4
|
|||
|
|||
Hi Ken,
Select Col B and do Formatconditional 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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Format Titles | Excel Discussion (Misc queries) | |||
copy conditional format as ACTUAL format | Excel Discussion (Misc queries) | |||
make a conditional format the default | Excel Discussion (Misc queries) | |||
Copying a conditional format | Excel Worksheet Functions | |||
Conditional Format With SUMIF | Excel Worksheet Functions |