Multiple Columns

[cjg1901]

Hi all

I have 2 sheets:

Sheet 1:
Employee Name / DOB
Smith, John / 19/08/1875
Richards, Tom / 14/06/1965
Jones, Wayne / 17/05/1978
Brown, Emily / 17/04/1985

Sheet 2:
Employee Name / DOB / Attended?
Smith, John / 19/08/1875 / No
Jones, Wayne / 17/05/1978 / No

Sheet 1 is a list of all employee names that were booked onto February's training course in January.

Sheet 2 is of the employees that failed to attend the training course - of those that failed to attend, I need to know if they are on Sheet 1 - in other words - if the ones that failed to attend were booked in January from Sheet 1.

So basically I need to know if those that didn't attend the training course (sheet 2) were booked onto the course in January (sheet 1).

Please help?!

Thanks
cjg1901

 

[BGY23]

You will need a unique identifier for each employee. Ideally there clock/ employee number or National Insurance Number.

Failing the above, create your own table of all employees and give each a unique number and put this table on sheet 3.

On sheet 1 and 2 when you input the data you should include this number in a separate column. Now you simply do a vlookup on sheet2 for the employee number on sheet 1 and if it's there you'll get the employee number, so they were booked on the course. If it's not there you will get #N/A which means they weren't booked on the course.

You could create a unique reference on sheets 1 & 2 by concatenating 1st name, 2nd name and DOB and then do the vlookup. You will run into trouble if you get 2 employees with the same name born on the same day.

 

[cjg1901]

I have my common reference - employee number now in both sheets. I have put the employee numbers in column A now.

What I need now is the formula to establish if the employees that missed the training course in sheet 2, were booked onto the training course in January (sheet 1).

Thanks
cjg1901

 

[BGY23]

So now all you do, is in a column on sheet 2 is a vlookup to see if the employee number appears on sheet 1.

Assume people on course employee number is on sheet 1 A:1 to a:20

Sheet 2 in a spare column say D
In D1 =vlookup($a1,sheet1!$A1:$A20,1,0)

If the result shows the employee number then that employees number is in sheet 1 if the result is #N/A then that employees number does not appear on sheet 1.