Unique entries
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Unique entries

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    107
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hi
    I am using excel to record licence numbers and i want to ensure that i don't enter the same licences keys twice. i know i can sort each time and search for duplicates but this will not be practical.
    thanks for your help.
    Macker

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,790
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-04-18 02:02, Macker72 wrote:
    Hi
    I am using excel to record licence numbers and i want to ensure that i don't enter the same licences keys twice. i know i can sort each time and search for duplicates but this will not be practical.
    thanks for your help.
    Macker
    Lets suppose that the licence keys are entered in A from A2 on.

    Activate A2 (which is as yet empty).
    Activate the option Data|Validation.
    Select Custom for Allow.
    Enter as formula:

    =COUNTIF($A$1:A1,A2)=0

    Activate OK.

    Copy down A2 as far as needed.

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-18 02:02, Macker72 wrote:
    Hi
    I am using excel to record licence numbers and i want to ensure that i don't enter the same licences keys twice. i know i can sort each time and search for duplicates but this will not be practical.
    thanks for your help.
    Macker
    If your records are in Column A.

    Goto insert>name>define, type a name that mean something, like RecordRange.

    in the Refers To box type

    =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

    press add

    while in the Define Names box create a name like UniqueRecords

    the Refers to box put:

    =COUNTA(Sheet1!$A:$A)-IF(LEN(RecordRange)>0,SUMPRODUCT(1/COUNTIF(RecordRange,RecordRange)))

    press add.

    Goto Data>Validation>Custom and the in Formula box type

    =UniqueRecords=0

    should do the trick.
    "Have a good time......all the time"
    Ian Mac

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Try with this example workbook..


    download file nos.27 ("uniqueentries")


    http://www.pexcel.com/download.htm

    you can see the code.

    ni****h desai
    http://www.pexcel.com

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com