Good Evening,
I am trying to create a revision list for pupils after they have sat their Maths mock just before half term. I have analysed every question for every pupil and now I want excel to identify which topics each pupil needs to work on.
I want Excel to do this by looking up cells in in a row which contain an "i" (for incomplete or not attempted), a "0" (attempted but zero marks), or any question without full marks, prioritised in this order. Where excel finds any of these conditions I want it to display the topic (found in row 2 of the same column) in a cell on sheet 2. Obviously giving pupils a list of every single topic they got wrong in a test is demoralising so I only want an output of ten topics (so ten cells excel can output to).
For Example,
Pupil A got "i" in AA5, AC5, BA5, BB5 and BG5, so on sheet 2, in cells C5:G5, I want them to output the topics stated in AA2, AC2, BA2, BB2 and BG2.
But I want ten targets, so now we have run out of "i" I want to look up "0". There are 6 cells which contain a 0 in row 5, at this point I don't care which topics are prioritised within the reds so any 5 of those 6 topics on sheet 2, H5:L5 need to be displayed.
In this case we now have ten targets so pupil A is done, however if we only had 8/9 targets excel should then lookup any 1s, 2s ect until the 10 cells C5:H5 are filled.
I feel that what I want to do goes well outside my excel know-how so a pointer in the right direction of what I should research into or a suggestion about how I could do it differently or even if you know exactly how to tackle my problem, any help would be much appreciated =)
Please find attached a copy of my workbook paper analysis.xlsx
Edit: ignore the "m" in the worksheet, it just stands for still to be marked. (didn't have a ruler or protractor until halfway through marking)
Bookmarks