Results 1 to 6 of 6
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    IMPORTANT - Prevent duplicates bug! (Excel 2000 >)

    There is a technique in Excel that allows you to prevent duplicate entries into a field in a list. You would use the Validation Feature in the Data Menu to do this. By selecting Custom, you can add a formula like: =COUNTIF($A$1:$A$10,A1)=1 to check and then dis-allow duplicates.
    HOWEVER: I was told that if a person copies a duplicated value and pastes it onto the cells that have the validation, it accepts the value and does NOT complain.
    THEIRFORE, this technique has a flaw and can not be 100% relied upon!

    If anyone has a alternative / preventative technique, please post a reply. It would be most appreciated!
    Cheers
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IMPORTANT - Prevent duplicates bug! (Excel 2000 >)

    This is not specific to prevent duplicates, it's a problem with Validation in general. There is no check during paste or paste special.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: IMPORTANT - Prevent duplicates bug! (Excel 2000 >)

    Thx. This is interesting...and a bit of an improvement for the next version.

    Maybe we can add condition formatting to check for duplicates too. Using this in combination with the validation will ensure the user is aware of the duplicate. In conditional formatting use Formula is : =COUNTIF($A$2:$A$19,A2)>1and shade font to red!
    Regards,
    Rudi

  4. #4
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: IMPORTANT - Prevent duplicates bug! (Excel 200

    That is the method I use. There is a good article on it - Excel Conditional Formatting & Validation for Duplicates in Excel

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IMPORTANT - Prevent duplicates bug! (Excel 2000 >)

    You can also use the Worksheet_Change event:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
    Application.EnableEvents = False
    For Each oCell In Intersect(Target, Range("A1:A10"))
    If WorksheetFunction.CountIf(Range("A1:A10"), oCell) > 1 Then
    oCell.Select
    MsgBox "Duplicate deleted!", vbExclamation
    oCell.ClearContents
    End If
    Next oCell
    Application.EnableEvents = True
    End If
    End Sub

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: IMPORTANT - Prevent duplicates bug! (Excel 2000 >)

    Thx Tony for the link. Some useful info there.
    Hans, thx for the code. This is a better alternative to the validation as it actually deletes the cells even if its copied. Many thanx for this!
    Regards,
    Rudi

Posting Permissions

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