Match 2 columns of data and delete duplicates Closed

Question

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.


Configuration: Windows 7 / Internet Explorer 8.0

venkat1926 · Answer

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

Match 2 columns of data and delete duplicates

1 response

Similar discussions

Subscribe To Our Newsletter!