Restrict user to update duplicate value

[Davesh Garg]

I have been struck in using multiple VBA coding, however not getting what I require. Hope someone will assist me in resolving my issue.

There are certain entries that user is going to enter in excel in Column A, Column B, Column C, Column D and Column E. I require that user can update any value in Column A, Column B, Column C, Column D but as soon as, user is updating duplicate value of concatenate of all columns then user will get some message and cell value of last column E will delete. There are chances that user can copy and paste values from previous row, then also they’ll get an error message and they will not be able to copy that duplicate entry. However, if entries of concatenate all values in a row is unique, then user is able to paste it.

Below is a description with example and detail of my requirement:

	Column A	Column B	Column C	Column D	Column E	Concatenate	Requirement
Row 1	X1	X2	X3	X4	X5	X1X2X3X4X5	No issues, as it’s a unique value
Row 2	X1	X2	X3	X4	X6	X1X2X3X4X6	No issues, as it’s a unique value
Row 3	X1	X2	X3	X4	X6	X1X2X3X4X6	User should get error message, and value (X6) in Column E, Row 3 should be deleted
Row 4	X2	X1	X3	X4	X5	X2X1X3X4X5	No issues, as it’s a unique value
Row 5 (copy and paste from Row 4)	X2	X1	X3	X4	X5	X2X1X3X4X5	User should get error message, and value (X5) in Column E, Row 5 should be deleted

I really had an issue from last one week to resolve this issue at the earliest, but is not able to resolve. Please help me out on this. Please let me know if any additional information is required. I am really thankful for your assistance.

Thanks - Dave

 

[Yard]

Hi, do you actually want to use VBA? Simplest way would be to count the number of times the value has appeared already, and then have a message or conditional formatting to highlight the problem.

i.e. in cell H1:
=COUNTIF(F$1:F1,F1)

and copy down. Anything returning a value >1 highlights a problem.

 

[Yard]

You can also use Data Validation:

With the formula above in column H, you can add Data Validation to the columns A:E, like:

Data>DataValidation
Allow: Custom
Formula: $H7=1

This means when you enter a value which causes the COUNTIF function in column H to return a value >1, you get a validation message (which you can tailor)

 

[davesexcel]

Rub this code to find the duplicates.

Sub FindDups()
    Dim Sh As Worksheet
    Dim LstRw As Long, Rng As Range, c
    Set Sh = ActiveSheet

    With Sh
        LstRw = .Cells(.Rows.Count, "F").End(xlUp).Row
        For c = 2 To LstRw
            Set Rng = .Range("F2:F" & c)
            If Application.WorksheetFunction.CountIf(Rng, .Cells(c, "F")) > 1 Then
                .Cells(c, "E").ClearContents
            End If
        Next
    End With
End Sub

 

[Davesh Garg]

Thanks Yard and Dave for your help.
Yard - Data validation will not work in case of copy and paste a row to different row.

Dave - I need to auto restrict a user, and above vba code will not work automatically. Additionally, user will not concatenate cells. This I told for reference whether there will be any duplicate entry or not after concatenate all columns and back-end will restrict user to update cell and will delete last value entered by user.

Really appreciate your assistance and response. Please assist on my requirement. Thanks

 

[davesexcel]

Find a way to run the code, use the worksheet_change event if you have to