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

jamilfarhan

New Member
Joined
Feb 25, 2019
Messages
2
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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,514
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
if you have a unique identifier you could just use conditional format for showing duplicates
 

jamilfarhan

New Member
Joined
Feb 25, 2019
Messages
2
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

Moderator
Joined
Oct 23, 2004
Messages
10,514
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
change 1,000,000 to 7,000. Instead of 5 million cells to check you will look at 35,000
 

Watch MrExcel Video

Forum statistics

Threads
1,108,787
Messages
5,524,875
Members
409,608
Latest member
GigaPat

This Week's Hot Topics

Top