Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    New Lounger
    Join Date
    Mar 2014
    Posts
    14
    Thanks
    10
    Thanked 1 Time in 1 Post

    Post Excel database duplicate validation

    Masters,

    Good day!


    How to formulate or create database that will not allow duplicate values input ranging from column A to C.

    Ex:

    Column A Column B Column C
    c 3 4
    a 1 2
    b 2 3
    c 3 4 <---- Scenario (The excel should not allow or warn me if I input repeated values)

    Regards,

    Math

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Math,

    Here is some code that looks AT entries in columns A, B, and C for duplicates. The code will not check until all 3 cells on the same row are filled. If it finds a duplicate row, a message box will open and indicate the duplicated values and the matching row found. It is case sensitive meaning that column values f 1 2 will not equal F 1 2. If you prefer to be case insensitive then a minor adjustment could be made.

    HTH,
    Maud

    entryChk.png

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    '--------------------------------------
    'DECLARE AND SET VARIABLES
    Dim srtng As String, LastRow As Long, I As Long, Row As Long
    strng = ""
        If Not Intersect(Target, Range("A2:C100")) Is Nothing Then
            Row = Target.Row
    '--------------------------------------
    'BUILD STRING FROM COLUMN VALUES
            If Range("A" & Row) <> "" And Range("B" & Row) <> "" And Range("C" & Row) <> "" Then
                strng = ConvString(Range("A" & Row)) & ConvString(Range("B" & Row)) & ConvString(Range("C" & Row))
            End If
    '--------------------------------------
    'TEST STRING AGAINS CURRENT COLUMN VALUES
            LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
            For I = 2 To LastRow
                If strng = ConvString(Range("A" & I)) & ConvString(Range("B" & I)) & _
                    ConvString(Range("C" & I)) And I <> Row Then
                    MsgBox "Warning, duplicate entry " & _
                    Range("A" & Row) & " " & Range("B" & Row) & " " & Range("C" & Row) & _
                    " on row " & I
                    Exit Sub
                End If
            Next I
        End If
    End Sub
    
    
    Public Function ConvString(entry) As String
    '--------------------------------------
    'CONVERT NON STRINGS TO STRINGS
        strng = ""
        If IsNumeric(entry) Or IsDate(entry) Then
            strng = strng & Trim(Str(entry))
        Else:
            strng = strng & entry
        End If
        ConvString = strng
     End Function

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    Mathdarkmoon (2015-03-24)

  4. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Maud

    ..here's another method.
    I use formulas for the data entry checks rather than vba.
    (I used Excel2003 as no version was specified)

    zeddy
    Attached Files Attached Files

  5. The Following User Says Thank You to zeddy For This Useful Post:

    Mathdarkmoon (2015-03-24)

  6. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Maud

    I tried yours, and although it gives the warning, it doesn't prevent the duplicate record being added.
    With my version, it stops you entering a duplicate record, whether the 3 cells are filled or not.
    And also, if by deleting a cell it would now make that record a duplicate of some other record, then this is also shown.
    But I've discovered another issue with my version, so I'm working on it again....

    zeddy

  7. The Following User Says Thank You to zeddy For This Useful Post:

    olives (2015-04-07)

  8. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You can do it directly with data validation alone. Using the example workbook, select A2:C25, choose Data - Validation, select Custom and enter this formula:
    =SUMPRODUCT(($A$2:$A2=$A2)*($B$2:$B2=$B2)*($C$2:$C 2=$C2))=1
    Then uncheck the 'Ignore blanks' option and OK out.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. The Following 2 Users Say Thank You to rory For This Useful Post:

    Mathdarkmoon (2015-03-24),olives (2015-04-07)

  10. #6
    New Lounger
    Join Date
    Mar 2014
    Posts
    14
    Thanks
    10
    Thanked 1 Time in 1 Post
    @Maud

    Hi sir, I have attached the example data for exact coding in VBA..

    @zeddy

    Hi sir, tried you solution but a little buggy as it will not allow me to cancel data input and the code should check 3 cells before it will say duplicate input (in this case it doesn't allow me to input in the 3rd cell if the first and second cell has same value)

    @rory

    I want your solution but don't know how to use it. LOL

    I tried data validation but still it allows me to input duplicate values...



    BTW, thanks to all of you for your fast response.

    God speed!

    Regards,

    Math
    Attached Files Attached Files

  11. The Following User Says Thank You to Mathdarkmoon For This Useful Post:

    olives (2015-04-07)

  12. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Math,
    In your worksheet, which columns correspond to Col A, B, and C in your opening post?

  13. The Following User Says Thank You to Maudibe For This Useful Post:

    olives (2015-04-07)

  14. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Assuming still Columns A,B,and C.

    HTH,
    Maud
    Attached Files Attached Files

  15. The Following 2 Users Say Thank You to Maudibe For This Useful Post:

    Mathdarkmoon (2015-03-23),olives (2015-04-07)

  16. #9
    New Lounger
    Join Date
    Mar 2014
    Posts
    14
    Thanks
    10
    Thanked 1 Time in 1 Post
    Sir,

    Sorry for lack of information, can you revise the code sir to validate from Column A to F (Location, Source, Material, Oreclass, Pile No, Series No)? I am making a pivot and I don't want to repeat the one row data to prevent it from calculating redundant data(s).

    Thanks,

    Math
    Last edited by Mathdarkmoon; 2015-03-23 at 23:51.

  17. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Math

    In the attached file [rz-data-duplicates-validation-v2.xls] I fixed the reported issues, and additionally dealt with 'pasting' into the data entry area. Rory's solution is the simplest if you aren't concerned with data being pasted (pasting can overwrite data validation). This file is included for those following the original request.

    In the second attached file [TRIAL V1-rz1a.xlsm],I have incorporated the features to deal with your example file and extended check columns. This uses two named cells [countCol] and [dupesCell] and formulas in hidden columns [K:N]. Data records are checked down to row50 (this could be extended of course), and conditional formatting has been applied to the range [A4:F50]. As the duplicate-check columns have now increased from 3 to to 6, I have also added my CONCAT function to make the hidden column [K] formula simpler.

    Note:
    While changing the values in a detected duplicate record, you may unintentionally change the entries to now make it a duplicate of some other record (i.e. anywhere in the block). This will be detected, and you will need to change the record entries again, until that record is a unique record.

    zeddy
    Attached Files Attached Files

  18. The Following 2 Users Say Thank You to zeddy For This Useful Post:

    Mathdarkmoon (2015-03-24),olives (2015-04-07)

  19. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Math,

    Here is my revised version to accommodate the additional columns you specified.

    HTH,
    Maud
    Attached Files Attached Files

  20. The Following 2 Users Say Thank You to Maudibe For This Useful Post:

    Mathdarkmoon (2015-03-24),olives (2015-04-07)

  21. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Maud

    Your solution does warn the User about a duplicate record (for columns A to F as requested), but does not prevent the duplicate.
    It is much trickier to warn and prevent a duplicate record being entered.
    Rory's method of using data validation does warn and prevent the duplicate record, providing you don't 'paste' data which overwrites the data validation.
    I wouldn't consider my solution as perfect, but it attempts to deal with 'prevention' as well as 'warning'.
    I would be grateful for any improvements!

    zeddy

  22. The Following User Says Thank You to zeddy For This Useful Post:

    olives (2015-04-07)

  23. #13
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Try this. It will find and cancel duplicates in col a-f. If you only want the same column, change accoringly
    '=========
    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lr As Long
    If Target.Count > 1 Then Exit Sub
    If Target.Column > 6 Or Len(Target) < 1 Then Exit Sub

    lr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastC ell).Row
    If Application.CountIf(Range(Cells(2, 1), Cells(lr, "f")), Target) > 1 Then
    MsgBox "duplicate "
    Target = ""
    End If
    End Sub
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  24. The Following 2 Users Say Thank You to Supershoe For This Useful Post:

    Mathdarkmoon (2015-03-24),olives (2015-04-07)

  25. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Don

    A nice piece of code.
    When I delete cells [D69] it doesn't prevent the duplicate records which result.

    zeddy

  26. The Following User Says Thank You to zeddy For This Useful Post:

    olives (2015-04-07)

  27. #15
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Zeddy, I don't understand what you are saying. Send me privately if desired.
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  28. The Following User Says Thank You to Supershoe For This Useful Post:

    olives (2015-04-07)

Page 1 of 2 12 LastLast

Posting Permissions

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