Setting up a grading matrix, vlookup doesn't help

Hi there,
I 'foolishly' offered to help a local school set up some standard forms etc! All have gone OK except for this one which is driving me nuts. I have tried vlookups, if functions etc, just can't get it working... I suspect it needs some VBA...

I have attached what I am trying to achieve. Input tab has the data, basically a set of grades for students, one grade at age 8, the other grade at age 11. I am trying to present this in a matrix, see Output tab. I want to automatically place the student name(s) in the cell that corresponds to their two grades. I have manually done the first 6 names.
teacher grading tool.xlsx

Your assistance would be much appreciated.
Thanks

Hi,

I can only see three manually-entered results, though I think I get the picture.

However, you don't mention what the results should be if two or more students have identical grades. My first thought was comma-separated in the same cell, though, above a certain number, this could become quite ungainly, not to mention the fact that it would then make a formula-based solution problematic and therefore require some VBA.

Please clarify.

Regards

Or another approach:

Please Login or Register  to view this content.

I was going to have a go at this but the competition was just too quick.

For my own benefit, I put the code into the workbook so I could see how it worked. The result is attached.

One question for OllyXLS: your routine seems to double up in some instances, although not all. Why is that?

Regards, TMS

Originally Posted by TMShucks

One question for OllyXLS: your routine seems to double up in some instances, although not all. Why is that?

Ah, looks like you didn't delete the example results which were pre-filled in the output sheet. Apologies - I should have set my routine to clear the output range first.

@OllyXLS: that's exactly right. I copied the sheet and renamed it so that a) I had the structure and b) I could compare the results.

Regards, TMS

@Rossg59b: Thanks for the rep.

Final option (maybe) using arrays:

Please Login or Register  to view this content.

Regards, TMS