filter data and delete double records

[**dan**]

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?

 

[Peter_SSs]

**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]

Hi,

Try this,

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]

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]

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

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**]

Thanks everyone! I sorted it out now.
Thanks for all the help!

regards, Danielle