Excel - Comparing Lists 1

[jonsi]

I have a sheet (JobCourse) with a long list (4,000 records) of all employees and which course they should attend dependent on their job

I have another sheet (Incomplete) with a shorter list (750 records)of all employees and the courses they have not yet completed

I would like to flag up those from the Incomplete Sheet onto the JobCourse Sheet.

Each Course can be identified by a basic identifier plus their staff number so making it unique.

Is there a formula to do this?

thanks
Jonsi

hwyl
Jonsi B-)
"If an apple a day keeps the Doctor away ...why don't Daleks live in Orchards?"

 

[jonsi]

found it! Should have done a SEARCH on here beforehand

=IF(COUNTIF(Incomplete!B:B,JobCourse!M4)=0,"Completed","INCOMPLETE")

hwyl
Jonsi B-)
"If an apple a day keeps the Doctor away ...why don't Daleks live in Orchards?"

 

[SkipVought]

Hi,

Hmmmmm???

Seems to me that you have TWO criteria to compare: The EMPLOYEE and the COURSE.

Let's say this is the incomplete list
[pre]
Employee Course
Skip A-1
Fred B-2

[/pre]

and that this is the complete list
[pre]
Emp Crs
Skip A-1
Skip B-2
Fred B-2
Fred C-1
[/pre]

Then a formula to COUNT occurrences of BOTH criteria...
[pre]
Emp Crs Count
Skip A-1 =SUMPRODUCT((Employee=F2)*(Course=G2))
Skip B-2
Fred B-2
Fred C-1
[/pre]

...resulting in...
[pre]
Emp Crs Count
Skip A1 1
Skip B2 0
Fred B2 1
Fred C1 0
[/pre]

So all the ZEROS are EMPLOYEE/COURSE not in the incomplete list

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]