compare columns on different sheets (same workbook) and delete rows where no match found

[davidmyers]

Hi,
Can't find a solution, and need a little help.
If possible I'd like to this with a formula.
Compare values in

Complaints!B2:B1350 (Whole column starting B2) In this range the values are not unique - same value on many rows.

with the range:

Memp-Only!A2:A143 (Whole column starting A2) Here all values are unique

I want to delete all rows in Complaints sheet that do not have a matching value in Memp-Only

Thanks for any help
David

 

[hiker95]

davidmyers,

So that we can get it right on the first try, can we see your actual raw data workbook, worksheets Complaints, and, Memp-Only?

You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com

 

[Peter_SSs]

If possible I'd like to this with a formula.

I want to delete all rows ...

Given that a formula cannot delete a row from a worksheet ..
- a macro could delete rows
- formulas coupled with a manual process could delete rows
- formulas could create a new list somewhere else (where?) that excluded the values you talk about deleting

Which of the above appeals? Or do you have something else altogether in mind?

 

[davidmyers]

Thanks Peter,
I found this piece of vba code that did the job:

Sub DeleteNotMatch()
Dim i As Integer
Const sh1Col As String = "B" ' << sheet1 data in col A, change
Const sh2Col As String = "A" ' << sheet2 data in col A, change
Dim ws1 As Worksheet, ws2 As Worksheet
Dim r1 As Long, r2 As Long
Set ws1 = Sheets("Complaints")
Set ws2 = Sheets("Memp-Only")
r1 = ws1.Cells(Rows.Count, sh1Col).End(xlUp).Row
r2 = ws2.Cells(Rows.Count, sh2Col).End(xlUp).Row
For i = r1 To 2 Step -1
For Each r In ws2.Range(sh2Col & "2:" & sh2Col & r2)
If ws1.Cells(i, sh1Col).Value = r.Value Then GoTo myNext
Next r
ws1.Cells(i, sh1Col).EntireRow.Delete
myNext:
Next i
End Sub

Hope it helps someone
David