Excel Excel problem

[cwarner]

What a great site...I'm hoping someone can help with my Excel problem.

I work for a career school and need to schedule courses for students but cannot schedule a new course until they have taken the pre-requisite course. So:

Col.A student's name
Col. B lists the courses the student still needs to take to complete their degree.
Col. C lists the pre-requisites.

Col.A Col.B Col.C
Alison bu100 no pre-req.
Alison bu125 bu100
Alison bu140 bu110
Alison bu150 bu130
Alison bu175 bu160
Bob mt115 mt100
Bob mt120 mt115
Bob mt 130 mt120
Carl bu100 no pre-req.
Carl bu130 bu110
Carl bu140 bu130
Carl bu150 bu145

In the above example for Alison, on the second line, she needs to take bu125 (Col.B) but bu125 requires that she's already taken bu100. However, we can see on Alison's first line (Col.B) that she still needs to take bu100, so she cannot yet take bu125.

Same thing occurs with Bob (3rd line) where he needs to take mt130 which requires that he's already taken mt120,. However, we can see on Bob's second line that he still needs to take mt120, so he cannot yet take mt130.

Again, same thing with Carl (third line, Col.B) where he needs to take bu140 which requires first taking bu130, but he cannot because we see on Carl's second line that he still needs to take bu130.

I need a formula that checks the pre-requisite courses (Col.C) against the courses that the student still needs to take (Col.B) and then returns some type of response (i.e: a "1" or a text like "YES", etc.) only for each individual student. By this I mean I only care about Alison's pre-req. courses matching against her still to be taken courses. Alison's pre-requisite courses are irrelevant to Carl's pre-requisite courses.

I've tried =match, =find, =index, vlookups and anything else I can find on the web and nothing seems to work.

Any help will be greatly appreciated.
thanks.

 

[Flona2012]

I think u need to creat a key
in column d do the key which will be [=ColumnA & "-" &ColumnB]
the key will look like "Alison-bu100"
and then in ColumnE write this formula [=if(C2="no pre-req.", "Yes",if(ISNA(Match(A2&"-"&C2, $D:$D,0)),"Yes","No"))

The formula i wrote comes in this sequence
1- first check if ColumnC Row 2 Value is "no pre req." if so then the value is yes as this course has no pre-request course, Student can take it

2- if ColumnA & "-" & ColumnC Doesnt exist in Key then This Student Took the pre-Request and he can take this course

3- Else its "No"


i hope this will help

 

[cwarner]

Thanks so much for offering a solution. Unfortunately, this didn't quite work for what I need. In the simplest form, I just need to find if any course in Col C (pre-req.) matches up against any course in Col B for only Alison, only Bob, only Carl. Keep in mind that if Alison is a new student, she'll have perhaps 20 new courses she'll need to take and therefore, a lot of pre-reqs involved. There will be multiple times that a pre-req will match up against a number of new courses for her. I need to match the pre-req against the new courses Alison (only) needs to take and then return the new course # that matches, as often as there are "hits", just for Alison. Then this formula needs to be copied down to show results for Bob (only), Carl (only), etc., for more than 700 different students.

thanks again for the reply.

 

[Flona2012]

hi cwaner sorry for the fomatting but am replaying from my phone,,,, as i understood from u ,,, u want the search for pre-request to be for a certain student each time,,,,but on the other hand thats why i included student name in the search key,,,so it will not look for bu100it will look for Alison-bu100,,,, i hope this is what u needed,,,, send feed back please

 

[cwarner]

After looking at it again and a slight tweak, I think I can get what I want. I'm moving into the next step and may be back for more great advice. THANK YOU!!