Hi everyone,

Just wondering if I may ask for some help/guidance/assistance or examples to automate a process of checking some data that is currently undertaken poorly with an "Index and match" function. I know some VBA coding, but this is out of my depth. I know it is not going to be a simple few lines of code to do what I need.

To give some idea, I will try and outline the issue below.

I Need to compare the data in WB1 with the data in WB2, as there is some double ups (which need to remain) I need to look up the payroll number and the email address to confirm the employee name. At present the process is completed annually using the “Index and match” formula in columns K & L. one looks up the payroll number and if it finds it, then payroll number is added to the cell, if not N/A is added. The next column looks up the email address and does a similar thing. Then the data is validated by confirming the existence of a payroll number in both columns. If there is an N/A then people must look at each person, try and marry them up with the work location, payroll number, email address etc etc. there is normally several hundreds to thousands of names that need correcting.

Workbook 1
Sheet containing training data - Ranges ("A2:J1250")
Employee Number
Name (some double ups exist)
Email address

Workbook 2

Sheet containing payroll and user data - Ranges ("A1:P98900")
Employee Number
Name
Email address

There are several issues with the data on WB1, and how it has been collected/reported

There are several employees with the same name, sometimes this is legit and other times they work at different locations covered by the data or they have been trained multiple times and they still need the historical data.
They have a different name, e.g they put down their name as Deb Smith, when their payroll name is Deborah Smith.
Sometimes the payroll number is recorded as “not supplied”, 1234567, 1111111 etc.
Sometimes the email address is different.
I know there will still be some issues and this will not solve them all, just trying to speed things up and make life easier and minimise further human checking and errors where possible.

Any assistance would be appreciated