Comparing two documents and making a list of names that are missing

[tonywatsonhelp]

Hi Everyone,
I need a macro that can compair two open documents find what names are missing and make a list

So this is what i need:

I have two open documents the main one is Called "Monthly Data.xlsm"
and the other is "Users.csv"

the macro will be run on the main one,
so "Monthly Data.xlsm" has a Sheet"Employees" in this sheet we have all employees and in column Z we have an Identifying Number (Unique to that person)
"Users.csv" is a csv i download from our wages software that list all the employees in the company,
in theory both list should be the same but to often the CSV has more names that master

so what i'm thinking is,
to get a macro that when run goes to "Users.csv" looks down column "B" and compares the numbers to the numbers in "Monthly Data.xlsm "Sheet"Employees" column z
if every number in the CSV is in the employees sheet then messagbox "All OK"
if not make a list of the numbers missing from the csv but also the names (the numbers are in coloumn B, First names in D and last names in G, take this data and make a list in sheet "Employees" From AD2

mesagebox " The following names are missing from the system" (then if possible list the number and name in the mesage box as well)

hope someone can help me with this?
Thanks

Tony

 

[Fluff]

How about

Sub tonywatson()
   Dim Cl As Range
   Dim Ary As Variant
   Dim wsUser As Worksheet, wsData As Worksheet
   Dim r As Long
   
   Set wsData = ThisWorkbook.Sheets("Employees")
   Set wsUser = Workbooks("Users.csv").Sheets(1)
   ReDim Ary(1 To wsusers.Range("B" & Rows.Count).End(xlUp).Row, 1 To 3)
   
   With CreateObject("scripting.dictionary")
      For Each Cl In wsData.Range("Z2", wsdate.Range("Z" & Rows.Count).End(xlUp))
         .item(Cl.Value) = Empty
      Next Cl
      For Each Cl In wsUser.Range("B2", wsUser.Range("B" & Rows.Count).End(xlUp))
         If Not .Exists(Cl.Value) Then
            r = r + 1
            Ary(r, 1) = Cl.Value
            Ary(r, 2) = Cl.Offset(, 2).Value
            Ary(r, 3) = Cl.Offset(, 5).Value
         End If
      Next Cl
   End With
   If r > 0 Then
      wsData.Range("AD2").Resize(r, 3).Value = Ary
      MsgBox "You have " & r & " names missing"
   Else
      MsgBox "All ok"
   End If
End Sub

 

[tonywatsonhelp]

Thank you Fluff,
this is great
Thanks
Tony

 

[Fluff]

You're welcome & thanks for the feedback.