This will do 90% of what you asked for.
The problem is you have a crappy data set.
I have reattached the file with the working code and Highlighted all of the instances where I had to inference the data.
Mytest1.xlsm
What you've asked is possible - I just don't have time to resolve the code.
Public S1cMerge_status
Sub Main()
AddColumn
DetermineStatus
End Sub
Private Sub AddColumn()
i = 1
Do While Sheet1.Cells(1, i).Value <> ""
If Sheet1.Cells(1, i).Value = "Merge_status" Then
S1cMerge_status = i
Exit Sub
End If
i = i + 1
Loop
Sheet1.Cells(1, i).Value = "Merge_status"
S1cMerge_status = i
End Sub
Private Sub DetermineStatus()
S1cKEY = 1
S1cCountry = 3
S1cEname = 4
S1cJob = 6
S2cKEY = 1
S2cCountry = 3
S2cEName = 4
S2cJob = 5
j = 2
Do Until Sheet1.Cells(j, S1cKEY).Value = ""
If Sheet1.Cells(j, S1cCountry).Value <> "" Then S1vCountry = Sheet1.Cells(j, S1cCountry).Value Else S1vCountry = "EMPTY"
If Sheet1.Cells(j, S1cEname).Value <> "" Then S1vEName = Sheet1.Cells(j, S1cEname).Value Else S1vEName = "EMPTY"
If Sheet1.Cells(j, S1cJob).Value <> "" Then S1vJob = Sheet1.Cells(j, S1cJob).Value Else S1vJob = "EMPTY"
Sheet1.Cells(j, S1cMerge_status).Value = ""
k = 2
Do Until Sheet2.Cells(k, S2cKEY).Value = ""
'STATUS 1 - IF EName AND Job MATCH AND ARE NOT "EMPTY"
If (S1vEName <> "EMPTY" And S1vJob <> "EMPTY") Then
If InStr(1, Sheet2.Cells(k, S2cEName).Value, S1vEName) <> 0 Then
If InStr(1, Sheet2.Cells(k, S2cJob).Value, S1vJob) <> 0 Then
Sheet1.Cells(j, S1cMerge_status).Value = 1
End If
End If
'MsgBox Sheet1.Cells(j, S1cMerge_status).Value & vbNewLine _
' & S1vEName & " : " & Sheet2.Cells(k, S2cEname).Value & vbNewLine _
' & S1vJob & " : " & Sheet2.Cells(k, S2cJob).Value
End If
'STATUS 2 - Country AND EName MATCH AND Job IS "EMPTY"
If Sheet1.Cells(j, S1cMerge_status).Value <> 1 Then
If (S1vCountry <> "EMPTY" And S1vEName <> "EMPTY" And S1vJob = "EMPTY") Then
If InStr(1, Sheet2.Cells(k, S2cCountry).Value, S1vCountry) <> 0 Then
If InStr(1, Sheet2.Cells(k, S2cEName).Value, S1vEName) <> 0 Then
Sheet1.Cells(j, S1cMerge_status).Value = 2
End If
End If
'MsgBox Sheet1.Cells(j, S1cMerge_status).Value & vbNewLine _
' & S1vCountry & " : " & Sheet2.Cells(k, S2cCountry).Value & vbNewLine _
' & S1vEName & " : " & Sheet2.Cells(k, S2cEName).Value & vbNewLine _
' & S1vJob
End If
End If
k = k + 1
Loop
'STATUS 0 - IF NOT STATUS 1 OR 2
If (Sheet1.Cells(j, S1cMerge_status).Value <> 1 And Sheet1.Cells(j, S1cMerge_status).Value <> 2) Then Sheet1.Cells(j, S1cMerge_status).Value = 0
j = j + 1
Loop
End Sub