Check if a value is found twice in a range

Hello,
I have to check if a value is found twice in a range and display an error if
Yes.
Lets suppose a range B3:B29 composed of several values not sorted (for ex :
250,412,359,1,6,8,45,18,412).
As 412 is found already in second position (and last one) I would like to
change color of Cell B30 from nothing to Red. Of course could be 18 which
appearing twice or more or any other number...

Thanks for you help and creativity !!!

I forgot to say that macros are not welcome... cause of security rules.
Sorry !

> Hello,
> I have to check if a value is found twice in a range and display an error if
> Yes.
> Lets suppose a range B3:B29 composed of several values not sorted (for ex :
> 250,412,359,1,6,8,45,18,412).
> As 412 is found already in second position (and last one) I would like to
> change color of Cell B30 from nothing to Red. Of course could be 18 which
> appearing twice or more or any other number...
>
> Thanks for you help and creativity !!!

One way is to use conditional format

Select the range e.g. a1:a6 then apply conditional format as

formula is =COUNTIF($a$1:$a$6,a1)>1

and set format to use red text when condition is true ( i.e.when item appears > 1 )

both duplicate (or more) values will be changed to red

You'll need someone else's help to get it to format just the first value though, sorry.

Regards

Max

If there is data in rows a1:a10, put this formula into conditional
formatting for cell a1 then use the format painter to copy the conditional
formatting throughout the range of data. (Format>conditional formatting )

=COUNTIF($A$1:$A$10,A1)>1



"François" <[email protected]> wrote in message
news:[email protected]...
I forgot to say that macros are not welcome... cause of security rules.
Sorry !

> Hello,
> I have to check if a value is found twice in a range and display an error
if
> Yes.
> Lets suppose a range B3:B29 composed of several values not sorted (for ex
:
> 250,412,359,1,6,8,45,18,412).
> As 412 is found already in second position (and last one) I would like to
> change color of Cell B30 from nothing to Red. Of course could be 18 which
> appearing twice or more or any other number...
>
> Thanks for you help and creativity !!!

On Fri, 13 May 2005 01:12:03 -0700, François
<[email protected]> wrote:

>Hello,
>I have to check if a value is found twice in a range and display an error if
>Yes.
>Lets suppose a range B3:B29 composed of several values not sorted (for ex :
>250,412,359,1,6,8,45,18,412).
>As 412 is found already in second position (and last one) I would like to
>change color of Cell B30 from nothing to Red. Of course could be 18 which
>appearing twice or more or any other number...
>
>Thanks for you help and creativity !!!

In Cell B3:

Format/Conditional Formatting/Formula Is: =COUNTIF($B$3:B3,B3)>1
Then format to taste.

Then, using the Format Painter, copy that format down as far as is necessary.

(Alternatively, select B3:Bn before typing in the above formula).


--ron