Mismatch

[Ramesh1482]

I have two files of Excel and I need to remove the common data from the files , so that there is no mismatch hence giving me one file with main data and common data on both the files is removed.
Eg.
File 1 File 2
1. Name 1. Name
D P Shukla D P Shukla
S K Jain P M Jain

I need Only D P Shukla name in File 1 and common name in File 2 is removed.

Please advice

Regards,
Capt R Pandey

[charles]

Ramesh1482,

Welcome to the forum.
The following code you can place in "File1" code module.
You will need both files open.
I placed remarks with in the code for your benefit.
You will or may need to change the sheets name to that of the actual worksheets for file 1 and file 2
This also assumes that both files are separate workbooks.

Code: Select all

Sub Remove_MatchedData_File2()
''' This code goes in File 1 module '''
''' You must have both files open ''
'' This assumes the Names are in column A
'' You will need to name Sheet1 to the actual worksheet names
Application.ScreenUpdating = False
Dim Myname As String
Dim lrow As Long
Dim Slrow As Long
Dim Scel As Variant ''this is a variable for the search in file 2
Dim i As Long
lrow = Sheets("Sheet1").Range("A65536").End(xlUp).Row ''get last row in column A
'' now we will activate file 2 and then search for the names.
Application.Workbooks("book2").Activate ''' change book2 to file2 name
For i = 2 To lrow
    '' we get the first name in file 1.
    Myname = ThisWorkbook.Sheets("Sheet1").Cells(i, 1).Text ''sheet name for file 1
    '''' now set the range to search ''
    Slrow = ActiveWorkbook.Sheets("Sheet1").Range("A65536").End(xlUp).Row '' sheet name for file 2
    With Sheets("Sheet1").Range("A1:A" & Slrow) '' sheet name for file 2
        Set Scel = .Find(Myname, LookIn:=xlValues)
        If Not Scel Is Nothing Then
            ''' we found the name in file 2 delete the row its on
            '' if name not found nothing will happen
            Cells(Scel.Row, 1).EntireRow.Delete
        End If
    End With
    ''' now we will look at the next name in file 1
Next i
End Sub