Exam class organiser

[lordyosch]

Hi,

I've been wracking my brain for this and googling to no success

I need to schedule a high school year group's revision classes. Allocation is based on target grades.
I want students who have a Grade D to be assigned to a list

I've tried using index based formulae to return class names but it isn't working for me.

	Maths	English	Science	French
Bill	A	B	D	F
Bob	B	D	D	C
Tony	D	F	F	D
Sue	A	A	A	A

<tbody>
</tbody>

So, I want the formula to return Science for Bill
English and Science for Bob
Maths and French for Tony
Nothing for Sue.


Can you guys help me?

Thanks,

Jay

 

[RonB1111]

Enter in F2 and copy down:

=SUBSTITUTE(TRIM(IF(B2="D",B$1,"")&" "&IF(C2="D",C$1,"")&" "&IF(D2="D",D$1,"")&" "&IF(E2="D",E$1,""))," "," and ")

 

[lordyosch]

That's great, thank you! I've never had cause to use substitute or trim before!

Can I be awkward and add an additional criterion?

Could the 'responses' be in consecutive columns?

Eg, the response for Tony would be [F4]Maths [G4]French -ignoring the columns containing other grades?

Thank you,

Jay

 

[RonB1111]

This is a CSE formula for F2 - you need to confirm it by holding down Ctrl-Shift-Enter simultaneously, do not just hit the enter key:

=IF(COUNTIF($B2:$E2,"D")>=COLUMNS($B2:B2),INDEX($B$1:$E$1,SMALL(IF($B2:$E2="D",COLUMN($B2:$E2)-COLUMN($B2)+1),COLUMNS($B2:B2))),"")

Now copy it down to F5.
Now select F2:F5 and copy across.

If your actual data has more columns of classes than your sample data, modify the formula by changing the four instances of $E to your last actual column (preceded by a $). Then place the formula in row 2 of any column to the right of that last column making sure to confirm with Ctrl-Shift-Enter. Finally, copy the formula down and then select all formulas in that column and copy across. If you need help adapting the formula, let me know which columns and rows contain your actual data.

This link may help in understanding what the formula is doing, although it's oriented towards a result from a single column rather than a single row:
BET: Microsoft Excel Functions Advanced - INDEX & SMALL - Particular Occurrence

 

[lordyosch]

Fantastic!

That's EXACTLY what I was after!

Thank you so much


(The actual data set contains 207 students studying about 10 each from 23 subjects!)

 

[RonB1111]

You're welcome!