Excel - Compare two workbooks and copy data 1

[HacH]

Hi,

I have two MS Excel sheets, both with similar data but not the same. One sheet contains all employee records e.g. employee no, surname, firstname, grade, pay, hours worked etc. The other sheet contains sickness data and has details of employee number, sickness days, type of sickness etc.

As not all employees have a sickness record in the sickness sheet but have an employee record in the employee records sheet. I would like to merge the data so that where employee number from the sickness sheet matches, I would like to copy the sickness data line corresponding to that employee to the employee records sheet.

Please see example below:

EMPLOYEE RECORDS SHEET

EmpNo Surname FirstName Post Ref Grade ....
34872 Smith Jones MTS1 MT1 ....
23442 James Watson SHD3 AD3 ....

EMPLOYEE SICKNESS DATA

EmpNo DaysSick TypeOfSick ...
34355 6 Self Cert ...
34872 2 Un-cert ...


If you look at the above example, EmpNo 34872 appears in both sheets, where such a match is found I would like to copy the SickDays, TypOfSick etc to the end of the Employee Record in the EMPLOYEE RECORDS SHEET.

It should look something like this:

EmpNo Surname FirstName .. SickDays TypeOfick ...
34872 Smith Jones .. 2 Un-Cert ...

Can anyone please help with this?

Thanks very much.

Mamoon

 

[arthurbr]

Hi Mamoon,

by using the VLOOKUP function , you will be able to achieve your goal.
Embed it in a IF function so that you will not get the #N/A error when the cells are empty
(actually using Access would be easier)

Good luck