lookup?

[johnpdavey]

i have a set of data as follows
A1 name of student
B1 - I1 subjects studied, ie Ma, Eng, Sci
A2 total point score for student in A1
B2 - I2 grade for subjects, ie A, B, G
cell A3 next student......

i have this data for 160 students.
the grades run from A - G and then U
cells B1 to I1, (and B3 to I3 for the next student etc,) do not all have entries as some students did not do Sci or Pe or....
Cell B1, etc, does not always contain Ma as it depends on the combination of subjects taken.

i only need the Ma data. so what i would like is some way of extracting the student name, which will be in cells A1, A3, A5 etc and the grade associated with Ma, which will be dirctly under the Ma entry which could be anywhere in the row between columns B and I

eg<pre>
A B C D E F G H I
JOE Ma Eng Sci Pe Hi Ge Art
12 A C G U E E F
BOB Eng Sci Ma
6 E F U


i would like
JOE A
BOB U</pre>

thanks in advance for any help
This message was edited by Juan Pablo G. on 2002-08-27 13:54

 

[Juan Pablo González]

Try this formulas:

Libro6.xls
	A	B	C	D	E	F	G	H
2	JOE	Ma	Eng	Sci	Pe	Hi	Ge	Art
3	12	A	C	G	U	E	E	F
4	BOB	Eng	Sci	Ma
5	6	E	F	U
6
7
8
9	JOE	A
10	BOB	U
Hoja3

 

[johnpdavey]

Juan
thanks for the help
it works great.
can you please explain something to me?
i can work out the cell references so i can use it on my data set but can you tell me what the ...)-1,o,2,256)2
and....)-1,0,1,256)0
do at the end of the ofset statement so that i can understand what is going on and adapt it for future use?
thanks again for the help
john

 

[PaddyD]

Hi John,

1) Never, ever set up a spreadsheet like this again!! Only ever have one row per entry, one set of column headings & live with the spaces in your data.


2) This isn't that dissimilar to what Juan posted, I've just broken the stages up so you can see them rather than do it all at once:

Book5
	A	B	C	D	E	F	G	H
1	JOE	Ma	Eng	Sci	Pe	Hi	Ge	Art
2	12	A	C	G	U	E	E	F
3	BOB	Eng	Sci	Ma
4	6	E	F	U
5
6		WhatStartCell?	WhatRow?	WhatCol?	Where'stheresult?	What'stheresult?
7	JOE	A1	1:1	2	2:2	A
8	BOB	A3	3:3	4	4:4	U
9
Sheet1

Comments:


Names - you will need to extract a list of just your pupil's names so that we can work of a proper list (the rest assumes there are no duplicate names)

What Start Cell:

=ADDRESS(MATCH(A7,$A$1:$A$4,0),MATCH($A$7,$A$1:$A$4,0),4,1)

This gets the cell address of where a particular name is in Col A. Given that you haven't got that much data, you could change $A$1:$a$4 in the formula to A:A so that it would still work when you added more data.

What Row?

=RIGHT(B7,1)&":"&RIGHT(B7,1)

Just on way of getting the row that the subject headings are on for a particular pupil.

What Col?

=MATCH("Ma",INDIRECT(C7),0)

Hunts for the position of "Ma" in the range indirect(c7) which in this case in the row 1:1 (check out the help for indirect to see how it works)

Where's the result?

=RIGHT(B7,1)+1&":"&RIGHT(B7,1)+1

Just another way of getting the row reference for where the results are (i.e. one row down from where the pupil's name is)

What's the result?

=INDEX(INDIRECT(E7),1,D7)

we now know the row we want & the column we want, so we can stick these values in an index function to return the final result.

This could all be done with a lot less typing, but like I said I thought I'd make it explicit.


Post back if you need further explanations.

paddy

 

[johnpdavey]

thanks for the explenation Paddy

ps i didnt set up the spreadsheet, its what i got from the exam board, i am just trying to extract the data for my subject, i teach highschool maths, then i will do the same for the other subjects

john

 

[PaddyD]

ps i didnt set up the spreadsheet, its what i got from the exam board

In which case hunt them down & kill them. No-one who does that sort of thing to a speadsheet has any right to be judging anyone!