GMC The Macro Man
Board Regular
- Joined
- Mar 23, 2023
- Messages
- 85
- Office Version
- 2021
- Platform
- Windows
Hi Team, I hope you are all doing well
I have 400 students who will sit at least 5 exams from a list of 25 subjects. The report is downloaded from the mainframe in a continuous table form so it looks like this.
It will show the list of 400 students and how they scored on the 1st subject, and then on the bottom line it will show the 400 pupils on the 2nd subject, then on the bottom line it will show the 400 on the 3rd subject.. and so on ... It will continuously add this 25 times for the 25 subjects.
What I am trying to achieve is from this data table, to count, the total of NAT Levels each individual pupil sat at "Nat 3, Nat4, Nat 5 & Nat 6" and how many of these Levels had a grade of less than 7. (Less than 7 means a Pass).
I want my output to look like this it to look like this table below ( If you filter the main Data table at the bottom, you will "P12346" has only sat 3 at NAT 6 and all are below "7")
Here is the data i used for this conversation, but the size of this would be 400 pupils multiplied by 25 subjects so 10k rows. ( I Don't mean to confuse you here)
I hope someone can help here
Thanks
I have 400 students who will sit at least 5 exams from a list of 25 subjects. The report is downloaded from the mainframe in a continuous table form so it looks like this.
St Maurice High School - June 2024.xlsx | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | ID No | Forename | Year Group | Nat Level | Grade | ||
2 | P12345 | Aaliyah | S6 | ||||
3 | P12346 | Abby | S6 | Nat 6 | 4 | ||
4 | P12347 | Aidan | S6 | ||||
5 | P12348 | Arran | S6 | Nat 6 | 6 | ||
6 | P12349 | Ayden | S6 | ||||
Sheet3 |
It will show the list of 400 students and how they scored on the 1st subject, and then on the bottom line it will show the 400 pupils on the 2nd subject, then on the bottom line it will show the 400 on the 3rd subject.. and so on ... It will continuously add this 25 times for the 25 subjects.
What I am trying to achieve is from this data table, to count, the total of NAT Levels each individual pupil sat at "Nat 3, Nat4, Nat 5 & Nat 6" and how many of these Levels had a grade of less than 7. (Less than 7 means a Pass).
I want my output to look like this it to look like this table below ( If you filter the main Data table at the bottom, you will "P12346" has only sat 3 at NAT 6 and all are below "7")
St Maurice High School - June 2024.xlsx | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
H | I | J | K | L | M | N | O | P | Q | |||
1 | ID No | Year Group | Nat 3 | 7 | Nat 4 | 7 | Nat 5 | 7 | Nat 6 | 7 | ||
3 | P12346 | S6 | 0 | 0 | 0 | 0 | 0 | 0 | 3 | 3 | ||
12 | P12355 | S5 | 0 | 0 | 0 | 0 | 2 | 1 | 0 | 0 | ||
29 | P12372 | S4 | 1 | 1 | 2 | 1 | 0 | 0 | 0 | 0 | ||
Sheet3 |
Here is the data i used for this conversation, but the size of this would be 400 pupils multiplied by 25 subjects so 10k rows. ( I Don't mean to confuse you here)
I hope someone can help here
Thanks
St Maurice High School - June 2024.xlsx | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | ID No | Forename | Year Group | Nat Level | Grade | ||
2 | P12345 | Aaliyah | S6 | ||||
3 | P12346 | Abby | S6 | Nat 6 | 4 | ||
4 | P12347 | Aidan | S6 | ||||
5 | P12348 | Arran | S6 | Nat 6 | 6 | ||
6 | P12349 | Ayden | S6 | ||||
7 | P12350 | Aidan | S6 | ||||
8 | P12351 | Aimee Marie | S6 | Nat 6 | 3 | ||
9 | P12352 | Alexa Ann | S6 | ||||
10 | P12353 | Alisha Selina | S6 | ||||
11 | P12354 | Andrew Lukas | S6 | Nat 6 | 8 | ||
12 | P12355 | Zoe McCann | S5 | ||||
13 | P12356 | Aiden | S5 | ||||
14 | P12357 | Aimee Kathleen | S5 | Nat 5 | 7 | ||
15 | P12358 | Alleisha | S5 | ||||
16 | P12359 | Brandon David | S5 | ||||
17 | P12360 | Abbie | S5 | Nat 5 | 5 | ||
18 | P12361 | Bethany | S5 | ||||
19 | P12362 | Caitlin | S5 | ||||
20 | P12363 | Carly | S5 | Nat 5 | 3 | ||
21 | P12364 | Dylan | S5 | ||||
22 | P12365 | Adam | S4 | ||||
23 | P12366 | Aimee | S4 | Nat 4 | 1 | ||
24 | P12367 | Bobby David | S4 | ||||
25 | P12368 | Cole | S4 | ||||
26 | P12369 | Corry | S4 | Nat 4 | 7 | ||
27 | P12370 | Aaron Anthony | S4 | ||||
28 | P12371 | Aaron | S4 | ||||
29 | P12372 | Beth | S4 | Nat 3 | 6 | ||
30 | P12373 | Aiden | S4 | ||||
31 | P12374 | Aimee | S4 | Nat 4 | 4 | ||
32 | P12345 | Aaliyah | S6 | ||||
33 | P12346 | Abby | S6 | Nat 6 | 2 | ||
34 | P12347 | Aidan | S6 | Nat 6 | 6 | ||
35 | P12348 | Arran | S6 | Nat 6 | 6 | ||
36 | P12349 | Ayden | S6 | ||||
37 | P12350 | Aidan | S6 | ||||
38 | P12351 | Aimee Marie | S6 | Nat 6 | 3 | ||
39 | P12352 | Alexa Ann | S6 | ||||
40 | P12353 | Alisha Selina | S6 | Nat 6 | 7 | ||
41 | P12354 | Andrew Lukas | S6 | ||||
42 | P12355 | Zoe McCann | S5 | Nat 5 | 9 | ||
43 | P12356 | Aiden | S5 | ||||
44 | P12357 | Aimee Kathleen | S5 | Nat 4 | 3 | ||
45 | P12358 | Alleisha | S5 | Nat 5 | 4 | ||
46 | P12359 | Brandon David | S5 | ||||
47 | P12360 | Abbie | S5 | Nat 4 | 5 | ||
48 | P12361 | Bethany | S5 | ||||
49 | P12362 | Caitlin | S5 | Nat 6 | 6 | ||
50 | P12363 | Carly | S5 | ||||
51 | P12364 | Dylan | S5 | Nat 5 | 2 | ||
52 | P12365 | Adam | S4 | ||||
53 | P12366 | Aimee | S4 | Nat 5 | 7 | ||
54 | P12367 | Bobby David | S4 | Nat 5 | 8 | ||
55 | P12368 | Cole | S4 | ||||
56 | P12369 | Corry | S4 | Nat 3 | 3 | ||
57 | P12370 | Aaron Anthony | S4 | Nat 3 | 5 | ||
58 | P12371 | Aaron | S4 | ||||
59 | P12372 | Beth | S4 | Nat 4 | 6 | ||
60 | P12373 | Aiden | S4 | ||||
61 | P12374 | Aimee | S4 | Nat 4 | 7 | ||
62 | P12345 | Aaliyah | S6 | ||||
63 | P12346 | Abby | S6 | Nat 6 | 2 | ||
64 | P12347 | Aidan | S6 | ||||
65 | P12348 | Arran | S6 | Nat 6 | 6 | ||
66 | P12349 | Ayden | S6 | ||||
67 | P12350 | Aidan | S6 | Nat 5 | 4 | ||
68 | P12351 | Aimee Marie | S6 | Nat 5 | 5 | ||
69 | P12352 | Alexa Ann | S6 | Nat 6 | 5 | ||
70 | P12353 | Alisha Selina | S6 | ||||
71 | P12354 | Andrew Lukas | S6 | ||||
72 | P12355 | Zoe McCann | S5 | Nat 5 | 3 | ||
73 | P12356 | Aiden | S5 | ||||
74 | P12357 | Aimee Kathleen | S5 | ||||
75 | P12358 | Alleisha | S5 | Nat 5 | 3 | ||
76 | P12359 | Brandon David | S5 | ||||
77 | P12360 | Abbie | S5 | Nat 5 | 2 | ||
78 | P12361 | Bethany | S5 | ||||
79 | P12362 | Caitlin | S5 | ||||
80 | P12363 | Carly | S5 | Nat 6 | 2 | ||
81 | P12364 | Dylan | S5 | ||||
82 | P12365 | Adam | S4 | Nat 5 | 6 | ||
83 | P12366 | Aimee | S4 | ||||
84 | P12367 | Bobby David | S4 | ||||
85 | P12368 | Cole | S4 | Nat 4 | 8 | ||
86 | P12369 | Corry | S4 | ||||
87 | P12370 | Aaron Anthony | S4 | Nat 4 | 6 | ||
88 | P12371 | Aaron | S4 | ||||
89 | P12372 | Beth | S4 | Nat 4 | 4 | ||
90 | P12373 | Aiden | S4 | Nat 4 | 9 | ||
91 | P12374 | Aimee | S4 | Nat 3 | 2 | ||
Sheet3 |