Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
"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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
"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. |
#5
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with data not getting plotted | Excel Discussion (Misc queries) | |||
Help with data not getting plotted | Charts and Charting in Excel | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
Another question on how to find duplicate data | Excel Worksheet Functions | |||
Finding duplicate data | Excel Worksheet Functions |