+ Reply to Thread
Results 1 to 5 of 5

detect Duplicate Data

  1. #1
    bdq17
    Guest

    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.

  2. #2
    Harlan Grove
    Guest

    re: detect Duplicate Data

    "bdq17" <bdq17@discussions.microsoft.com> 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. #3
    Max
    Guest

    re: detect Duplicate Data

    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" <bdq17@discussions.microsoft.com> wrote in message
    news:0558D407-E2BF-438F-A110-F9FCC40958FB@microsoft.com...
    > 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. #4
    Harlan Grove
    Guest

    re: detect Duplicate Data

    "Max" <demechanik@yahoo.com> 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. #5
    Max
    Guest

    re: detect Duplicate Data

    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" <hrlngrv@aol.com> wrote in message
    news:#uRPycxDFHA.4004@tk2msftngp13.phx.gbl...
    > "Max" <demechanik@yahoo.com> 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1