Match 2 columns of data and delete duplicates

[Closed]
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,

I have 2 worksheets with data that needs to be eliminated on sheet 1.
Sheet 1 column M contains a random list of numbers
Sheet 2 column A contains an organized list of numbers
Need to compare Sheet 1 column M data to Sheet 2 column A data and delete rows on Sheet 1 that match Sheet 2.
Basically each cell on Sheet 1 column M needs to be compared to entire column data of Sheet 2 column A to determine a match. Then delete the row. I am not to familiar with Excel macro coding. I can get to the macro coding section and logically figure out some of the basics but that is it.
Any help is greatly appreciated.

V/R,
Daniel mc.


1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
BEFORE DOING THIS SAVE YOUR FILE SAFELY SOME WHERE SO THAT DATA CAN BE RETRIEVED IF THERE IS A MES UP .

2. row no1. in both sheet are having column headings.

I am sure you know where to park the macro. try this macro

the macro is


Sub test() 

Dim r2 As Range, j As Long, k As Long, cfind As Range, x 
With Worksheets("sheet1") 
j = .Range("M2").End(xlDown).Row 
For k = j To 2 Step -1 
x = .Cells(k, "M").Value 
With Worksheets("sheet2") 
Set r2 = Range(.Range("A2"), .Range("A2").End(xlDown)) 
Set cfind = r2.Cells.Find(what:=x, lookat:=xlWhole) 
If Not cfind Is Nothing Then 
GoTo line1 
Else 
GoTo nextk 
End If 
End With 
GoTo nextk 
line1: 
.Cells(k, "m").EntireRow.Delete 
nextk: 
Next k 
End With 
End Sub