Lookup duplicates and Delete Rows

LJREdinburgh

New Member
Joined
May 20, 2014
Messages
46
Hi,

I have a database #1 of contact information consisting of e-mails, first name, last name, business name, etc. which contains many entries (rows) that need to be deleted. These entries would be deleted based on a secondary database #2 consisting of just e-mails.

If there are duplicates in the E-mails (both database column A) than those rows from database #1 should be deleted.

Is there an easy way to set this up? Or would it be easier to manually go through and delete duplicates? There are roughly 1000, hence why I am trying to find a quicker way.

Thanks
 

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.
Hiya
If the e-mail addresses as in EXACTLY the same format in both books, then this is pretty simple to do with VBA. If you can let me know the following I'll set something up.
a) The name of workbook #1 along with the sheet name
b) The name of workbook #2 along with the sheet name
c) Are there any blanks in Column A, in either workbook?
 
Upvote 0
Hiya
If the e-mail addresses as in EXACTLY the same format in both books, then this is pretty simple to do with VBA. If you can let me know the following I'll set something up.
a) The name of workbook #1 along with the sheet name
b) The name of workbook #2 along with the sheet name
c) Are there any blanks in Column A, in either workbook?

I am glad that you say this is pretty simple! It will save me a bit of legwork

A) Tidied Database , Sheet1
B) Bounces , Sheet1
C) No blanks in Column A, Data begins from cell A2 each time. Is usually a full e-mail address, but somtimes might not have @ symbol.

Thanks!
 
Upvote 0
Ok
Give this a go, put it in a standard module, in either workbook & ensure both books are open before you run it.

Code:
Sub E_Mail()

    Dim Dict As Scripting.Dictionary
    Dim ValU As Variant
    Dim Adds As Variant
    Dim DbSht As Worksheet
    Dim BoSht As Worksheet
    Dim UsdRws As Long
    Dim i As Long

With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With

    Set DbSht = Workbooks("Tidied Database.[COLOR=#ff0000]xls[/COLOR]").Sheets("Sheet1")
    Set BoSht = Workbooks("Bounces.[COLOR=#ff0000]xls[/COLOR]").Sheets("Sheet1")

    With BoSht
        Adds = .Range("A2", .Range("A" & .Rows.Count).End(xlUp))
    End With

    Set Dict = CreateObject("Scripting.Dictionary")
    With Dict
        For Each ValU In Adds
            If Not IsEmpty(ValU) Then
                If Not .Exists(ValU) Then .Add (ValU), Nothing
            End If
        Next ValU
    End With

    With DbSht
        UsdRws = .Range("A" & .Rows.Count).End(xlUp).Row
        For i = UsdRws To 2 Step -1
            If Dict.Exists(.Range("A" & i).Value) Then .Rows(i).Delete
        Next i
    End With

With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With

End Sub
You'll probably need to change the file extensions to suit.
 
Upvote 0
Ok
Give this a go, put it in a standard module, in either workbook & ensure both books are open before you run it.

Code:
Sub E_Mail()

    Dim Dict As Scripting.Dictionary
    Dim ValU As Variant
    Dim Adds As Variant
    Dim DbSht As Worksheet
    Dim BoSht As Worksheet
    Dim UsdRws As Long
    Dim i As Long

With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With

    Set DbSht = Workbooks("Tidied Database.[COLOR=#ff0000]xls[/COLOR]").Sheets("Sheet1")
    Set BoSht = Workbooks("Bounces.[COLOR=#ff0000]xls[/COLOR]").Sheets("Sheet1")

    With BoSht
        Adds = .Range("A2", .Range("A" & .Rows.Count).End(xlUp))
    End With

    Set Dict = CreateObject("Scripting.Dictionary")
    With Dict
        For Each ValU In Adds
            If Not IsEmpty(ValU) Then
                If Not .Exists(ValU) Then .Add (ValU), Nothing
            End If
        Next ValU
    End With

    With DbSht
        UsdRws = .Range("A" & .Rows.Count).End(xlUp).Row
        For i = UsdRws To 2 Step -1
            If Dict.Exists(.Range("A" & i).Value) Then .Rows(i).Delete
        Next i
    End With

With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With

End Sub
You'll probably need to change the file extensions to suit.

Received a Compile error: User-defined type not defined and the first line is highlighted i.e. Dim Dict As Scripting.Dictionary
 
Upvote 0
:oops:
One day I'll B... remember to put this into the post
In the VB editor select Tools > References > Microsoft Scripting Runtime making sure that the checkbox is ticked, rather than the line just highlighted.
Sorry about that
 
Upvote 0
:oops:
One day I'll B... remember to put this into the post
In the VB editor select Tools > References > Microsoft Scripting Runtime making sure that the checkbox is ticked, rather than the line just highlighted.
Sorry about that


Had to make a few tweaks here and there in the file path's but it has worked great!

Thanks again!
 
Upvote 0
(y)
Thanks for the feedback
Glad to be able to help
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

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
Back
Top