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

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,719
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,358
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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
 
Solution

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,719
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Thank you Fluff,
this is great
Thanks
Tony
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,358
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,196
Messages
5,623,305
Members
415,965
Latest member
Sixaside

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top