detect Duplicate Data

How do I get excel to automatically detect and notify when I've typed the
same number in two cells of the same column? I get a lot of duplicate product
numbers that I imput into column A of my spreadsheet. Example: If I type the
number 123456 into A1 I don't want that number in any other cell of column A
and I want excel to notify me if I type a duplicate.

"bdq17" <[email protected]> wrote...
>How do I get excel to automatically detect and notify when I've typed
>the same number in two cells of the same column? I get a lot of duplicate
>product numbers that I imput into column A of my spreadsheet. Example: If
>I type the number 123456 into A1 I don't want that number in any other
>cell of column A and I want excel to notify me if I type a duplicate.

Are you generating these product numbers yourself? If so, you're misusing
your computer. If you want distinct product numbers, just add 1 to the
previous product number - done.

One way via Data Validation ..

Select col A

Click Data > Validation
Make the settings:
Choose under "Allow:" : Custom
Formula: =COUNTIF($A$1:A1,A1)<=1
Click OK
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"bdq17" <[email protected]> wrote in message
news:[email protected]...
> How do I get excel to automatically detect and notify when I've typed the
> same number in two cells of the same column? I get a lot of duplicate
product
> numbers that I imput into column A of my spreadsheet. Example: If I type
the
> number 123456 into A1 I don't want that number in any other cell of column
A
> and I want excel to notify me if I type a duplicate.

"Max" <[email protected]> wrote...
>One way via Data Validation ..
>
>Select col A

With cell A1 active.

>Click Data > Validation
>Make the settings:
>Choose under "Allow:" : Custom
>Formula: =COUNTIF($A$1:A1,A1)<=1
>Click OK

Maybe, but two caveats. First, if the OP ever pastes text (from other
applications) into cells with validation, the validation is bypassed but
still in place, so it doesn't do anything to prevent duplicate entry.
Second, if any entries are text, Excel will treat text with different
numbers of trailing whitespace as distinct entries.

More technical, if the range in question is large, COUNTIF could become
slow. Better to apply validation to A2:A65536 and use the formula

=ISERROR(MATCH(A2,$A$1:$A1,0))

COUNTIF will interate through the entire range every time. MATCH will return
upon finding the first match, if there is one.

Was just going down the straight groove with the earlier suggestion <g>
Nonetheless, 2 good caveats to nail down here, and a better suggestion for
the OP to try .. Thanks, Harlan !

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Harlan Grove" <[email protected]> wrote in message
news:#[email protected]...
> "Max" <[email protected]> wrote...
> >One way via Data Validation ..
> >
> >Select col A
>
> With cell A1 active.
>
> >Click Data > Validation
> >Make the settings:
> >Choose under "Allow:" : Custom
> >Formula: =COUNTIF($A$1:A1,A1)<=1
> >Click OK
>
> Maybe, but two caveats. First, if the OP ever pastes text (from other
> applications) into cells with validation, the validation is bypassed but
> still in place, so it doesn't do anything to prevent duplicate entry.
> Second, if any entries are text, Excel will treat text with different
> numbers of trailing whitespace as distinct entries.
>
> More technical, if the range in question is large, COUNTIF could become
> slow. Better to apply validation to A2:A65536 and use the formula
>
> =ISERROR(MATCH(A2,$A$1:$A1,0))
>
> COUNTIF will interate through the entire range every time. MATCH will
return
> upon finding the first match, if there is one.
>
>