Using VBA to check duplicate entries and create MsgBox

[Joey_T92]

Hello,

I'm having a bit of trouble if someone would be able to help. I've had a look and can't seem to find anywhere this has been addressed previously.

I have CSV files containing customer data where each row is a new customer and the columns take the form: first name, last name, address 1, address 2, address 3, city, postcode, etc... I have a series of macros which manipulate the data in these files and then export it in multiple formats as required by various other software packages.

For reasons mostly outside of my control, some customers details are duplicated during the process which gathers this information, but I have a macro to identify and remove these duplicate rows. This macro essentially looks at the first name, last name, and address columns and removes one of the duplicates if all these fields match:

Sub DelDups()
    
    With Sheets("Master")
        .Cells.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7, 8)
    End With

End Sub

Unfortunately it has now become apparent that some customers are being entered in to the front end system twice but with two different addresses. This results in two orders being generated and dispatched as these duplicates are not detected by the current macro. What I want to do is have a macro I can run after the above has finished and which will check for duplicates in the first and last name fields (columns A and B), and if any are found it will trigger a MsgBox asking the user to manually check the raw CSV to identify the potential duplication and delete the entry with the incorrect address (if required).

Essentially:
If there are rows with duplicate values in columns A and B then pop up a message to signal this, if the yes button is clicked then continue processing, if the cancel/no button is clicked then abort any further processing.

Hopefully this makes sense. I'm new to VBA so I'm trying to learn, and what I've been able to do so far is great, but still find some of it very confusing!

Thanks very much for your help!

 

[Logit]

.
The following will check both columns (A&B) for duplicates. If located, it will highlight in yellow the second entry of the duplicate, then
popup a MsgBox advising "Duplicates".

Option Explicit

Sub CheckDupes()

   Dim Cl As Range
   Dim ValU As String
   Dim Rng As Range
  
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A1", Range("A" & Rows.Count).End(xlUp))
         ValU = Cl.Value & Cl.Offset(, 1).Value
         If Not .exists(ValU) Then
            .Add ValU, Nothing
         Else
            If Rng Is Nothing Then
               Set Rng = Cl
            Else
               Set Rng = Union(Rng, Cl)
               Rng.Interior.Color = vbYellow
            End If
         End If
      Next Cl
   End With
   If Not Rng Is Nothing Then
    MsgBox "Duplicates"
   End If

End Sub

 

[excel01noob]

Hi, looking for something similar.
How to adapt this to work with this:

-search for duplicates under column A,
- some can have spaces before and/or after the inserted data
-highlight the duplicated cells in yellow (all of them);
-message box to appear when duplicate is inserted or when trying to close the file via Save/save as