Duplicate Entries in a Column
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Duplicate Entries in a Column

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    In my worksheet I have a large area where cells are already "data validated" to only allow entries which are on a predetermined list. BUT I want to make sure the same entry cannot be entered twice in the same column. I also need to ignore some cells.

    Compounding this problem is that there are 3 cells in a column, skip a cell, 3 more cells, skip a cell and 3 more.

    For example:

    E7 - "GF"
    E8 - "PT"
    E9 - "QI"
    SKIP CELL E10
    E11 - "KJ"
    E12 - "XC"
    E13 - "DH"
    SKIP CELL E14
    E15 - "DJ"
    E16 - "BH"
    E17 - "SA"

    I want to make sure that the value for cell E15 is not the same as E7 (or any other cell in this column.) An optimal solution would be to eliminate the possibility of entering a duplicate value. I must do this for each column of my worksheet. My worksheet is approximately 30 columns wide. But I only want to be sure the entries are not duplicated in the columns (duplicates in the rows are acceptable and expected).

    Thank you for helping. I am stumped. AS usual. . . .

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Data Validation should be able to do this.
    Select the range that you want (say it's
    $E$1:$E$400, and go to Data | Validation
    Select Custom validation, and in the
    formula use
    =COUNTIF($E$1:$E$400,E1)=1

    To work with this restriction and using
    an allowed list, use
    =AND(COUNTIF($E$1:$E$400,E1)=1,COUNTIF(Allowed,E1)=1)

    Here, I assumed that the named range Allowed contains the allowable entries.

    HTH


    "Interfere? Of course we should interfere! Always do what you're best at, that's what I say!" -- The Doctor, Nightmare of Eden

  3. #3
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-12 09:30, Tim Francis-Wright wrote:
    Data Validation should be able to do this.
    Select the range that you want (say it's
    $E$1:$E$400, and go to Data | Validation
    Select Custom validation, and in the
    formula use
    =COUNTIF($E$1:$E$400,E1)=1

    To work with this restriction and using
    an allowed list, use
    =AND(COUNTIF($E$1:$E$400,E1)=1,COUNTIF(Allowed,E1)=1)

    Here, I assumed that the named range Allowed contains the allowable entries.

    HTH


    Hi Tim,

    Nice! Great post -- very clever.

    Bye,
    Jay

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

    Default

    Hi Tim, thanks for the nice formula. it works great.

    but how can i do validation on a form?

    pls let me know

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-04-12 10:29, LMF wrote:
    Hi Tim, thanks for the nice formula. it works great.

    but how can i do validation on a form?

    pls let me know
    There's no direct analog for Data Validation on a form. But that's not to say that you
    can't do it.

    The BeforeUpdate event has a boolean
    variable that keeps the focus on the control
    and prevents the Exit or AfterUpdate events
    from firing.

    I use this to run a validation routine on
    textboxes and the like: you can test
    for numeric or integral data, or anything
    else, e.g.:

    Private Sub TextBox1_BeforeUpdate(ByVal TryAgain As MSForms.ReturnBoolean)
    If TextBox1.Text Like "foo*" Then
    msgbox "...bar!"
    TryAgain = True
    End if
    End Sub

    HTH

    "Interfere? Of course we should interfere! Always do what you're best at, that's what I say!" -- The Doctor, Nightmare of Eden

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