Lookup data from 2 worksheets and display yes or no

[TiSwAs]

Hi, I very rarely use Excel and would appreciate some guidance...

I have 2 lots of data within one workbook
i) HR data containing Employee Number surname and Known as (actual employees)
ii) NT Data containing the same info. (logon accounts)

I would like to cross reference/search/vlookup the Employee Number from the 2 worksheets to see if the employees in the HR data has an NT logon account in the NT data worksheet - I would like it to return a YES or NO in a column within the HR Data worksheet dependant obviously if the data can be found or not.

Then what I can do is filter the No's to double check the information to see if one needs creating.

Please find attached a sample XLS document.

Thanks for your time.
Kind regards

[antoka05]

Try with this code:

Sub Macro1()
   Dim hrSh As Worksheet
   Dim nrSh As Worksheet
   
   Set hrSh = ThisWorkbook.Sheets("hr data")
   Set ntsh = ThisWorkbook.Sheets("nt data")
   
   lastrow = hrSh.Cells(Rows.Count, "a").End(xlUp).Row
   
   For r = 2 To lastrow
      myResult = Evaluate("isna(vlookup('" & hrSh.Name & "'!a" & r & ",'" _
                 & ntsh.Name & "'!a:a,1,false))")
      If myResult = True Then
         hrSh.Cells(r, "d") = "No"
      Else
         hrSh.Cells(r, "d") = "Yes"
      End If
   Next r
End Sub

Regards,
Antonio

[DonkeyOte]

Or alternatively

Public Sub NTCHECK()
Sheets("HR Data").Select
With Range(Cells(2, "A"), Cells(Rows.Count, "A").End(xlUp)).Offset(, 3)
    .FormulaR1C1 = "=IF(ISNA(MATCH(RC1,'NT Data'!C1,0)),""No"",""Yes"")"
    .Value = .Value
End With
End Sub

If you want to do without VBA

HR Data!D2: =IF(ISNA(MATCH(A2,'NT Data'!A:A,0)),"No","Yes")
copied down

[TiSwAs]

If you want to do without VBA

HR Data!D2: =IF(ISNA(MATCH(A2,'NT Data'!A:A,0)),"No","Yes")
copied down

Thank you both for the reply - i went with the none visual basic and it work great.

thanks again and you can mark this one as solved.

Kind Regards