I need a help

[Frank Kabel]

Hi
try the following
- select all cells in column B (assumption the data starts in row 1)
- goto conditional format and enter the following formula
=COUNTIF($A$1:$A$1000,B1)=0
- choose your format

 

[Bob Phillips]

Try using a formula of

=COUNTIF($A$1:$A$1000,B1)>0

in column B CF, and select a colour to highlight.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[JE McGimpsey]

take a look at

http://cpearson.com/excel/duplicat.htm

 

[Zdenka]

I have some figures in column A and the same figures in column B, but in
column B are some figures which are not in the column A. I want to highlight
all those figures in column B which are not in column A. If I use
conditional formatting, can I copy it from first cell where i make it, cause
I have about 5000 figures in column A and B. Thanks

 

[Frank Kabel]

Hi Bob
you probably meant
=COUNTIF($A$1:$A$1000,B1)=0
as the OP wants to highlight all cells which are not within column A

maybe too much Shiraz <vbg>

 

[Ken Wright]

maybe too much Shiraz <vbg>

You mean this is possible???? <g>

 

[Bob Phillips]

Surely, highlighting items in column A ipso fact highlights those not in A
(if you see what I mean)

Other than that .... well the bottle is empty, and it's onto Armagnac now.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Frank Kabel]

You mean this is possible???? <g>

Ken
seeing Bob's reply: No I don't think so - getting over to Armagnac
<vbg>

 

[Ken Wright]

rotflmao

 

[AlfD]

Lucky you: I had a German wine tasting tonight. 2 possibles out of 10
About my usual batting average.

Al

 

[Frank Kabel]

Hi Alf
what wines did you taste (I hope not red germany wines)

 

[Zdenka]

Thanks, but when I enter this formula I get a message: The formula you typed
contains an error.

 

[Frank Kabel]

Hi
works for me. Do you also get an error if you enter this formula in a
cell and if yes what error did you get?

 

[A.W.J. Ales]

Zdenka,

The formula Frank gave you IS correct.
Few questions :
1) Did you : Format / Conditional formatting / select Formula Is in the
dropdown box and then enter the formula Frank gave.
2) Do you have an European regional setting. If so your argument separator
in functions is ; instead of ,
You should change Franks formula to =COUNTIF($A$1:$A$1000;B1)=0 then

(You can check your setting by inputting any function via the functionwizard
and look how its arguments are separated once you've finished your input).


--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[copycollie]

Zdenka,

you can also try this:

-Format the backgroundcolor of column B to the highlightcolor.
-conditional format column B with 2 conditions
# first formula:
=LEN(B1)=o
set formatting to no bg-color.

#second formula:
=COUNTIF($A$1:$A$5000,B1)=0


Now should only see the cells highlighted which are not in in colum
A.

Regards,

copycolli

 

[Zdenka]

A.W.J.Ales thanks, it works with ; instead of , ! You are the best!!!

 

[Zdenka]

Also big thanks to Frank, and of course all of you.

 

[Frank Kabel]

Hi
put the following formula in column C (starting in C1):
=IF(COUNTIF($A$1:$A$1000;B1)=0,"*","")
and copy down

 

[Zdenka]

Now when I highlighted all figures in column B which are not in column A, is
it possible to put some sign (* or something) in column C by each
highlighted cell, cause this sign I can put as a custom in a filter, and see
all highlighted cells in a moment. Am I complicated??

 

[A.W.J. Ales]

Hi Frank,

Do you make a habit of answering a question before is it posted? (In my
thread you answer at 0:24 and the question is posted 0:25). That way I'll
never be able to beat you in speed of course ( is that the effect of shiraz
or armagnac ?)
Anyhow I think you mean : =IF(COUNTIF($A$1:$A$1000;B1)=0;"*";"")

(I know : It's an annoying item those argumentseparators)

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *