lookup? revisited

[johnpdavey]

hi
i'm not sure about the correct protocol for revisiting a topic but my earlier request is about 10 - 15 posts below this
http://www.mrexcel.com/board/viewtopic.php?topic=20282&forum=2&5

any way the problem that now arises because of the way the exam board has set up their spreadsheet is that their are two entries for En, (English).
there is english literature and english language, literature has only one grade, so in the cell under En i will have a grade such as D, but under En for language i will have two grades, something like D;C
some students will have done both english exams and will therefore have two En enries
so my question is this, is there anyway to modify the match / offset formulas so that they will pick up the single grades, or the double grades as required.
eg

A B C D E F G H I
JOE En En Ma Sci Art
12 D D;C A E B
BOB En Ma Sci
8 F G E
SUE Ma En Art
9 e D;E G

i would like
English literature
JOE D
BOB F
SUE
English Language
JOE D;C
BOB
SUE D;E

thanks in advance for your help
john

 

[PaddyD]

Hi John,

1) Best way to continue a thread is simply to post a reply to it - it'll be brought back to the top automatically.


2) Re the duplicate 'En' entries. My two-penneth worth...Long term, you'd be better off going in & making the 160 or so edits you need to to separate En Lit from En Lan. It would be possible to get them both returned, but it wouldn't be that pleasant. & why bastardise a reasonable solution just to cope with one set of recalcitrant data elements when they can be cleaned up fairly quickly? It is also more satisfying from a logical point of view in that you will have distinct column heading, making further calculations easier.

So - it can be done, but I'd suggest fixing the data.

Like I said in the previous post, your best option might be to kill the exam board employee who set the thing up liike this!

Paddy
This message was edited by PaddyD on 2002-08-27 16:09

 

[lenze]

Paddy wrote:
"Like I said in the previous post, your best option might be to kill the exam board employee who set the thing up like this!"

Death is TOO good for him.

 

[PaddyD]