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" 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 <atyahoo<dotcom
----
"bdq17" wrote in message
...
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" 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 <atyahoo<dotcom
----
"Harlan Grove" wrote in message
...
"Max" 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.