Previous Topic Next Topic

Match 2 columns of data and delete duplicates

Closed
Daniel Mc. - Nov 15, 2010 at 02:16 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Nov 15, 2010 at 09:29 PM
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.


Related:

1 response

Answer 1 / 1
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Nov 15, 2010 at 09:29 PM
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
0