Qualification check

[HarryLime]

I have a table of students who have been on two courses at our facility. For the purposes of what I am doing both courses need to be on the same tab.

Both courses need to be passed for the student to complete the qualification. The student can fail and retry the course.

What I am trying to do is show when the student has passed both courses. In the example below Jones has passed both course types at the first attempt and is given the qualification. Bloggs fails one course type but passes at the second attempt and gains the qualification.

The table below is a simplistic version of what I am using. There can be up to 90 students per course. A student who failed a course may not return until two iterations of the course has been run.

Any idea how I can automate this?

Course Type	ID number	Name	Course Result	Qual Gained
1​	1234​	Jones	Pass
1​	3456​	Bloggs	Fail
2​	1234​	Jones	Pass	Yes
2​	3456​	Bloggs	Pass
1​	3456​	Bloggs	Pass	Yes

 

[etaf]

you could try
=IF(COUNTIFS($B$2:B2,B2,$D$2:D2,"pass")=2,"YES","NO")
and copy down,
This assumes the course number does not matter, and its just a case of having the name with 2 pass