filter data and delete double records

**dan**

New Member
Joined
Oct 14, 2006
Messages
5
Hi,
I have two worksheets. Worksheet1 has two columns: column A with names and column B with email addresses.
(approx. 4500 addresses) Worksheet 2 has one column with approx 4900 email addresses. I now would like to delete all email adresses + corresponding names in Worksheet1 that are the same as the email addresses in Worksheet2.
What is the easiest way to do that?
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,562
Office Version
  1. 365
Platform
  1. Windows
**dan**

Welcome to the Mr Excel board!

This assumes the two worksheets are in the same Workbook and are actually named 'Sheet1' and 'Sheet2'. Also assuming in Sheet1 the names are in column A emails in column B and in Sheet2 the email addresses are in column A and that all columns have headings so the actual data starts in row 2.

1. In Sheet1 cell C2 (copied down): =ISNUMBER(MATCH(B2,Sheet2!A:A,0))
2. In Select column C by clicking its heading label
3. Data|Filter|AutoFilter
4. Click the drop-down in cell C1 and choose TRUE
5. Select all visible rows of data (apart from the heading row)
6. Edit|Delete Row
7. Select column C
8. Edit|Delete...|Entire Column|OK
 

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
Hi,

Try this,

Code:
Sub test()
Dim ws1 As Worksheet
Dim ws2 As Worksheet

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")

ws2.Range("C2:C" & ws2.Range("A" & Rows.Count).End(xlUp).Row).Formula = _
    "=A2&""#""&B2"

ws1.Range("C2:C" & ws1.Range("A" & Rows.Count).End(xlUp).Row).Formula = _
    "=ISNUMBER(MATCH(A2&""#""&B2,Sheet2!C:C,0))"
ws1.[C1] = "TEMP"

With ws1.Range("C1:C" & ws1.Range("A" & Rows.Count).End(xlUp).Row)
    .AutoFilter field:=1, Criteria1:="TRUE"
    .Offset(1, 0).Resize(.Rows.Count - 1, 1).EntireRow.Delete
    .AutoFilter
End With
ws1.Columns(3).Delete
End Sub

HTH
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,562
Office Version
  1. 365
Platform
  1. Windows
via PM
hi peter,

thanks for your fast reply!

I tried this but i got lost at the part where I had to select 'true'. Maybe its because I have a dutch version of Excel or an old version or something..
I copied the =ISNUMBER(MATCH(B2,Sheet2!A:A,0)) into column c2 and then copied it all the way down so next to each emailaddress in column b I had the same formula. Then I selected column C and Data-Filter-Autofilter. But when I click on the drop down that then appears I cannot choose 'true'. The options i get are (translated from dutch...):

-sort going up
-sort going down
-------------------
[all categories]
(top 10....)
(adjusted)
=ISNUMBER(MATCH(B2,Sheet2!A:A,0))
(empty cells)
(non-empty cells)

Which one do I choose here?

thanks for helping me out...

regards, Danielle
Danielle, perhaps there is a language issue here and my Dutch is non-existent. However, can you clarify what appears (in Dutch) in column C after you have entered the formula and copied it down? I would have thought there should be just a list of just two possible results.
 

jmckeone

Well-known Member
Joined
Jun 3, 2006
Messages
550
I posted pretty much the same scenario a couple days back on the following thread and got a working solution.

http://www.mrexcel.com/board2/viewtopic.php?t=237653

Code:
Sub DeleteByCompare()
Dim lRow As Long, rKeep As Range
Dim rRng As Range

Set rKeep = Worksheets("Keep").Range("A2", Worksheets("Keep").Range("A2").End(xlDown))
With Worksheets("Loc")
    For lRow = .Range("A2").End(xlDown).Row To 2 Step -1
        If rKeep.Find(.Range("A" & lRow), LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then .Rows(lRow).Delete
    Next
End With

End Sub
 

**dan**

New Member
Joined
Oct 14, 2006
Messages
5
Thanks everyone! I sorted it out now.
Thanks for all the help!

regards, Danielle
 

Forum statistics

Threads
1,137,366
Messages
5,681,068
Members
419,950
Latest member
BeckiJae

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