Hi,
I have a list of 10 names in column D1:D10.
I have three columns of data that I need to sort to get what I need from.
A1:A130 contains the number of tries between 1 and 6 that a student has attempted
B1:B130 contains the list of students names in attempt order. The names will be one of the listed names in D1:D10 but in attempt order
C1:C130 contains the results which are all between 0 - 1000
In columns E1 (student name) I want the name of the student starting with D1.
In column F1 (1st attempt) I want to search column B1:B130 for the student I have placed into E1 and if I get a match, I need to then to check the cell immediately to the left of the matched cell (this is the bit I am having difficulty with) and if that is set to "1", then I want to place the results from the cell immediately to the right of the name matched cell into F1 as the result.
Columns G1 - K1 will be the same but checking for a "2", "3", "4", "5", and finally "6" which correspond to the 2nd through 6th attempt. Some may only have 3 attempts.
I am struggling to find the right formula that will work.
My end result I want to look like this
E1 (student name), F1 (1st attempt score), G1 (2nd attempt score), H1 (3rd attempt score), I1 (4th attempt score), J1 (5th attempt score), K1 (6th attempt score)
then continue down until I have all the student information
E2 (student name), F2 (1st attempt score), G2 (2nd attempt score), H2 (3rd attempt score), I2 (4th attempt score), J2 (5th attempt score), K2 (6th attempt score)
E3 (student name), F3 (1st attempt score), G3 (2nd attempt score), H3 (3rd attempt score), I3 (4th attempt score), J3 (5th attempt score), K3 (6th attempt score)
.... etc until all 10 students and their corresponding results from each attempt has been recorded.
I hope this makes sense, but if not please let me know what else would be useful.
Paul
I have a list of 10 names in column D1:D10.
I have three columns of data that I need to sort to get what I need from.
A1:A130 contains the number of tries between 1 and 6 that a student has attempted
B1:B130 contains the list of students names in attempt order. The names will be one of the listed names in D1:D10 but in attempt order
C1:C130 contains the results which are all between 0 - 1000
In columns E1 (student name) I want the name of the student starting with D1.
In column F1 (1st attempt) I want to search column B1:B130 for the student I have placed into E1 and if I get a match, I need to then to check the cell immediately to the left of the matched cell (this is the bit I am having difficulty with) and if that is set to "1", then I want to place the results from the cell immediately to the right of the name matched cell into F1 as the result.
Columns G1 - K1 will be the same but checking for a "2", "3", "4", "5", and finally "6" which correspond to the 2nd through 6th attempt. Some may only have 3 attempts.
I am struggling to find the right formula that will work.
My end result I want to look like this
E1 (student name), F1 (1st attempt score), G1 (2nd attempt score), H1 (3rd attempt score), I1 (4th attempt score), J1 (5th attempt score), K1 (6th attempt score)
then continue down until I have all the student information
E2 (student name), F2 (1st attempt score), G2 (2nd attempt score), H2 (3rd attempt score), I2 (4th attempt score), J2 (5th attempt score), K2 (6th attempt score)
E3 (student name), F3 (1st attempt score), G3 (2nd attempt score), H3 (3rd attempt score), I3 (4th attempt score), J3 (5th attempt score), K3 (6th attempt score)
.... etc until all 10 students and their corresponding results from each attempt has been recorded.
I hope this makes sense, but if not please let me know what else would be useful.
Paul
