Selecting a number of requirements from a large list

I am working on developing a graduation requirements spreadsheet for my school that pulls in all the student data from their entire high school career. The goal is to match up the raw data with the graduation requirements. This amounts to about 17,000 lines of data. There is a mix of sections where some courses all students must pass in order to graduate but other sections require the student to take a certain number of courses from a larger selection of courses.

For example, all students must have at least 5 courses at the grade 12 level. Grade 12 courses are in our data system as having the course number of 120, 121, 122, 123. There are about 35 different courses that students can take at the grade 12 level.

What I want is for Excel to be able to look through the data, find the student name and whether or not they have a passing mark in at least 5 of these courses and fill in on the graduation sheet the course name and the mark.

Since there are many lines of data, the method used to populate the first worksheet grinds my computer to a halt when I try to run the data for all 1000 students.

I am including a sample worksheet with one student on the grad requirement sheets and his total high school history on the second sheet.

If anyone can give me some suggestions on a more calculation friendly way to populate the grad requirements sheet as well as how to have Excel scan for a set number of courses at a particular grade level I would be eternally grateful. I think that Pivot tables might be a solution but would I have to set up a pivot table for each of the grade 12 courses in one column and use VLOOKUP?

Thanks for taking a look.

The last criteria is just =COUNTIF(AI3:AM3, ">70")

I'm not really sure what you mean by the last criteria. Could you explain that a bit more? Thanks.

The one that counts the number of passing grades in the 12x-series classes.

Ok, so how does CountIF know the difference between a Gr 12 course and a Grade 11 course? I just need it to find 5 passing grade 12 marks but the formula as I see doesn't seem to show how it knows to just pull grade 12 marks.

Maybe I am missing something....

The formula references only the cells that contain 12th grade classes on the graduation sheet; that's the only sense in which it 'knows'.

To rearrange the data on the history tab, perhaps someone can help with a pivot table.

Okay, so if I arrange all the Grade 12 courses in a pivot table, I can use the COUNTIF Function to find the passing grade. Is there a way to put the name of the course above the mark (say in cell AV 1) as well as the mark in the cell below?