Returning top grade results

[aexley]

Hi,

I'm working in a school and I need to run a report where I can pull out all students who scored all "A's" for their grades, all "1's" for their effort and who scored both and "A" and a "1" in all subjects they took.

The basic data sheet looks like this:

StudentCode	Name	Subject Code	Subject	Grade	Effort
1000	Ben Smith	10PHYS	Year 10 Physics	A+	1
1000	Ben Smith	10PE	Year 10 PE	A	1
1000	Ben Smith	10MATH	Year 10 Math	A-	1
1001	Jill Brown	10PHYS	Year 10 Physics	B+	1
1001	Jill Brown	10DRAMA	Year 10 Drama	A	1
1002	Simon West	10PE	Year 10 Phys Ed	A+	2
1002	Simon West	10MUSIC	Year 10 Music	A-	2
1002	Simon West	10ENG	Year 10 English	A	1
1002	Simon West	10MATH	Year 10 Math	A-	1
1003	Sarah White	10PE	Year 10 Phys Ed	B-	2
1003	Sarah White	10DRAMA	Year 10 Drama	C	3

<tbody>
</tbody>

So the results expected should be:

All "A's" - Ben Smith, Simon West
All "1's" - Ben Smith, Jill Brown
All "A's and 1's" - Ben Smith

Any advice would be appreciated.

Thanks.

 

[liveinhope]

This I how I did it
- first make a unique list of names(col J)
- K = formula for how many subjects that student does =COUNTIFS($B$2:$B$12,$J2)
- L = formula how many subjects that student got A in =COUNTIFS($B$2:$B$12,$J2,$E$2:$E$12,"a*") NB this includes A- which strictly speaking is not n A
- M = formula did student get all A's =COUNTIFS($B$2:$B$12,$J2,$E$2:$E$12,"a*") =COUNTIFS($B$2:$B$12,$J2)


AL these formulas are for data rows 2 to 12 -adjust t suit your datal

Name	#subjects	#As	all A's	a and1	all a and 1
Ben Smith	3	3	TRUE	3	TRUE
Jill Brown	2	1	FALSE	1	FALSE
Simon West	4	4	TRUE	2	FALSE
Sarah White	2	0	FALSE	0	FALSE

<colgroup><col width="118" style="width: 89pt; mso-width-source: userset; mso-width-alt: 4315;"> <col width="119" style="width: 89pt; mso-width-source: userset; mso-width-alt: 4352;"> <col width="64" style="width: 48pt;" span="4"> <tbody> </tbody>

make a

 

[konew1]

Here is a solution without helper columns. It does require you to create a list of all students, removing duplicates, and will report on each. If you just want a list of those with all A1 then you will probably need VBA code.

Excel Workbook
	A	B	C	D	E	F
1	StudentCode	Name	Subject Code	Subject	Grade	Effort
2	1000	Ben Smith	10PHYS	Year 10 Physics	A+	1
3	1000	Ben Smith	10PE	Year 10 PE	A	1
4	1000	Ben Smith	10MATH	Year 10 Math	A-	1
5	1001	Jill Brown	10PHYS	Year 10 Physics	B+	1
6	1001	Jill Brown	10DRAMA	Year 10 Drama	A	1
7	1002	Simon West	10PE	Year 10 Phys Ed	A+	2
8	1002	Simon West	10MUSIC	Year 10 Music	A-	2
9	1002	Simon West	10ENG	Year 10 English	A	1
10	1002	Simon West	10MATH	Year 10 Math	A-	1
11	1003	Sarah White	10PE	Year 10 Phys Ed	B-	2
12	1003	Sarah White	10DRAMA	Year 10 Drama	C	3
13
14
15		Ben Smith	All A1
16		Jill Brown	not all A1
17		Simon West	not all A1
18		Sarah White	not all A1
Sheet2