Comparing two excel files and copy Unmatched

Hi John!

Thank you for the greetings! http://www.excelforum.com/images/icons/icon14.png

I have figured out the main part of the code. I have looked after on many forums and with a "little" assistance I have created the following code:

Sub matchReport()
    Dim id As String
    Dim cmo_fmo As String
    Dim last_row, j As Integer
            
    ' get last row of this workbook
    last_row = ActiveSheet.UsedRange.Rows.Count + 1
                
    ' MODIFY THIS ROW
    Set Source = Workbooks.Open("c:\3_-_Munka\Network\LAN\EXCEL FILE NAME")
    
    
    j = 2
    Do While (Source.Sheets("Raw data").Cells(j, 1).Value <> "")
    
        ' get values from source
        id = Source.Sheets("Raw data").Cells(j, 1).Value
        cmo_fmo = Source.Sheets("Raw data").Cells(j, 4).Value
            
        'find id
        ThisWorkbook.Activate
        Columns("E:E").Select
        Set cell = Selection.Find(What:=id, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
    
        If cell Is Nothing Then
            'if id not found -> insert row from source
            ActiveSheet.Cells(last_row, 1).Value = Source.Sheets("Raw data").Cells(j, 5).Value
            ActiveSheet.Cells(last_row, 2).Value = Source.Sheets("Raw data").Cells(j, 2).Value
            ActiveSheet.Cells(last_row, 3).Value = Source.Sheets("Raw data").Cells(j, 3).Value
            ActiveSheet.Cells(last_row, 4).Value = Source.Sheets("Raw data").Cells(j, 4).Value
            ActiveSheet.Cells(last_row, 5).Value = Source.Sheets("Raw data").Cells(j, 1).Value
            ActiveSheet.Cells(last_row, 6).Value = Source.Sheets("Raw data").Cells(j, 10).Value
            ActiveSheet.Cells(last_row, 5).Value = Source.Sheets("Raw data").Cells(j, 1).Value
            
            'inc last row
            last_row = last_row + 1
        Else
            'match found -> paste CMO / FMO value
            ActiveSheet.Cells(cell.Row, 4).Value = cmo_fmo
        End If
        
        j = j + 1
    Loop

    Source.Close
    
End Sub

Only one task remained but first I try to figure out then ask questions

While I am searching the next task would be -

If Column 'A' in "workbook name" "report" sheet contains :

• eoi then the data in column D (in the same row) will be 'FMO'
• SW then the data in column D (in the same row) will be 'CMO'

and this action is in loop until it reaches the last row. Maybe I can build it into the original code but maybe it would be better put in a separate sub.

sample.jpg

thanks in advance!