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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

mole999

Well-known Member
Joined
Oct 23, 2004
Messages
10,524
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

Well-known Member
Joined
Oct 23, 2004
Messages
10,524
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,478
Messages
5,831,913
Members
430,091
Latest member
Generally_confused

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top