need help in displaying message to the end user saying duplicates are entered. Please check

[jamilfarhan]

Hello everyone!

I am completely new to excel and would require help from any esteemed members in this forum.

I have an excel sheet which gets values from the SQL database. This excel is used by Customer service team who manually enter data and in this process I have seen people entering duplicates.

So I need someone how can help me display a message when a duplicate is found and cannot proceed further once the duplicate is eliminated.

What I have done so far is this

Went into data validation by clicking data -->data validation and I enter this formula

=COUNTIFS($A$2:$A$1000000,$A2,$B$2:$B$1000000,$B2,$C$2:$C$1000000,$C2,$D$2:$D$1000000,$D2,$E$2:$E$1000000,$E2) = 1
in the custom section

So, If someone types in and manually enters the details and completes a row,then it checks all the rows and works fine and displays correct message, but when someone does a COPY paste from a different worksheet or deliberately copies it from the same worksheet , the formula doesn't work and it doesn't display a message.

Would appreciate if someone can help me in this.

Please don't hesitate to contact me if further clarification Is required

Regards
Farhan Jamil

 

[mole999]

if you have a unique identifier you could just use conditional format for showing duplicates

 

[jamilfarhan]

if you have a unique identifier you could just use conditional format for showing duplicates

Hi

Thank you for your reply. This hasn't helped me. I possibly think macros will be a good option. I have tried googling it for a possible solution and tried some of the macros by editing it to a specific range, but still it hasn't helped me out. I have one macros which did help but it takes long time to respond saying that you have duplicates.

Possible reason :- I have nearly 7000 rows which is checking one value against another to identify duplicates and hence is taking time

The earlier formula I mentioned works perfectly with this macros but it takes long time to respond saying that you have duplicates.

Formula inserted in data validation:- =COUNTIFS($A$2:$A$1000000,$A2,$B$2:$B$1000000,$B2,$D$2:$D$1000000,$D2,$E$2:$E$1000000,$E2) = 1
Formula inserted in macros:-
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
For Each cell In Range("A2:E1000000")
If Not cell.Validation.Value Then
MsgBox "Duplicate value entered"
End If
Next
End Sub

Would appreciate help.

Please advise

Regards
Farhan Jamil

 

[mole999]

change 1,000,000 to 7,000. Instead of 5 million cells to check you will look at 35,000