complicated vlookup / if then / max value nesting

[willowshade]

I cannot say how many times I have ended up here looking for answers, and I usually find them. This time, I am trying to work what seems to me to be a very complicated problem. If I can get the formula right, it will save me dozens of hours a week.

I run a virtual lab, and not all of the programs we use have usable gradebooks, so I have to create my own. I end up creating 37 courses per grading cycle, and have students enrolled in all of them. The program will let me download a progress report in PDF, and I can hand enter everything, or as a CSV. I want to create a formula to let me use the CSV (saved as an excel workbook) and vlookup to automatically catch the grades and enter them into the gradebooks I create (I love macros for creating those).

So I have two excel workbooks, Gradebook, and Student Progress Reports. Gradebook changes frequently, but I can use a template to cut and paste the desired sections. Student Progress Reports is saved over every time I run it and I DO NOT want previos information to disappear if it is no longer in the new run.

I am including small samples of the workbooks.

Gradebook - a collection of worksheets for each core subject and electives.

Excel Workbook
	A	B	C	D	E	F
1	Algebra 1A	SS		TOT	Notes -A1231	Quiz -A1232
2	Last Name	First Name	ID	Algebraic Expressions	Algebraic Expressions	Quiz: Algebraic Expressions
3	Lopez	Claudette	1234567
Math

Excel Workbook
	A	B	C	D	E	F	G	H
67	Claudette	G	Lopez	1234567	Quiz: Algebraic Expressions	A1232	20%	Failed
68	Claudette	G	Lopez	1234567	Quiz: Algebraic Expressions	A1232	20%	Failed
69	Claudette	G	Lopez	1234567	Quiz: Algebraic Expressions	A1232	10%	Failed
70	Claudette	G	Lopez	1234567	Algebraic Expressions	A1231	N/A	No Score
71	Claudette	G	Lopez	1234567	Algebraic Expressions	A1231	N/A	No Score
72	Claudette	G	Lopez	1234567	Quiz: Algebraic Expressions	A1232	80%	Passed
73	Claudette	G	Lopez	1234567	Equations with Variables on Both Sides	A1331	N/A	No Score
Excel 2007 Student Progress Report - one massive page with all student work - can be run as smaller reports divided by course but that means running 37 different reports Student_Progress_Report

Excel 2007

I did take out a lot of extraneous columns to make it easier to see the relevant info.

So here is the dilema. The reports can include hundreds of kids in dozens of different courses, so am thinking I need to use an If then statement to match the ID numbers (columns C and D respectively) before looking at any further information. Once that is settled, I need vlookup to find the assignment names from row 2 in gradebooks, and match it to the name in column E in student progress reports. And since, as you can see, the students can take the same quiz up to 3 times, I need to to choose the max value from there.

In the workbook examples above, Claudette failed the test 3 times, then rewatched the videos, took better notes, and got help from a teacher. When she retook the quiz on her last chance, she got an 80. I want to take that 80, and put it in the right place in the gradebook and those 20s do not need to count.

I am currently hand entering everything, and as the program grows, so does my stress level, and it keeps me from being able to get other people to use the program for remediation in their own classes, because they dont want to search the progress reports for the grades.

I know this can be done, but the nesting is giving me massive headaches, so I was hoping that someone with more experience could help me find the solution. I am open to changing the methods I use, to trying anything to make keeping the grade records easier.

I have tried using a variety of nests, but I cannot get the right results returned, and that is using a pared down version, not even a full scale grading cycle.

I prefer using 2007 excel, if that makes a difference.

THank you in advance for anything anyone can do to help me.