GMC The Macro Man
Board Regular
- Joined
- Mar 23, 2023
- Messages
- 102
- Office Version
- 2021
- Platform
- Windows
Hello there
The spreadsheet below shows a list of pupils with some of the subjects they are going to sit this year.
The values under the subjects, represent the expected result they will achieve. The scale is 1 = Excellent and 8 = Very Poor.
I needed to filter out (on a separate sheet) any pupil who has a 7 or 8 listed in their expected results.
Thanks to FLUFF (Mr Excel MVP) he was able to assist me using the formula below which worked perfectly.
=LET(f,FILTER(Grading!A2:N11,MMULT(--(Grading!D2:N11>=7),SEQUENCE(COLUMNS(Grading!D1:N1),,,0))),IF(f="","",f))
The client has now asked if they could add another column after each subject with a Grading value of either 4,5,6 or 7.
On the 2nd image you can see I have added the extra columns (Shaded) but because this could also contain a "7" in the column, it follows the above formula and brings over data in that row.
Using Fluffs formula, I tried to pick just the columns that i required but i am failing big time
I need the formula that FLUFF gave me but only to filter on these columns, D15,F15,H15,J15,L15,N15,P15,R15,T15,V15, W15,X15,Y15
Can anyone help?
TIA
George
The spreadsheet below shows a list of pupils with some of the subjects they are going to sit this year.
The values under the subjects, represent the expected result they will achieve. The scale is 1 = Excellent and 8 = Very Poor.
I needed to filter out (on a separate sheet) any pupil who has a 7 or 8 listed in their expected results.
Thanks to FLUFF (Mr Excel MVP) he was able to assist me using the formula below which worked perfectly.
=LET(f,FILTER(Grading!A2:N11,MMULT(--(Grading!D2:N11>=7),SEQUENCE(COLUMNS(Grading!D1:N1),,,0))),IF(f="","",f))
The client has now asked if they could add another column after each subject with a Grading value of either 4,5,6 or 7.
On the 2nd image you can see I have added the extra columns (Shaded) but because this could also contain a "7" in the column, it follows the above formula and brings over data in that row.
Using Fluffs formula, I tried to pick just the columns that i required but i am failing big time
I need the formula that FLUFF gave me but only to filter on these columns, D15,F15,H15,J15,L15,N15,P15,R15,T15,V15, W15,X15,Y15
Can anyone help?
TIA
George
| Grading.xlsx | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
| 1 | Name | Class | Gender | Art & Design | Business | Chemistry | Dance | Design | Drama | English | French | Geography | Graphics | History | ||
| 2 | George | 4A | Male | 4 | 2 | 6 | 7 | 8 | 2 | |||||||
| 3 | Larry | 4A | Male | 3 | 3 | 3 | 3 | 3 | ||||||||
| 4 | Divya | 4A | Female | 1 | 1 | 1 | 8 | 7 | ||||||||
| 5 | Urvesh | 4A | Male | 5 | 4 | 7 | 7 | 2 | ||||||||
| 6 | Suzie | 4B | Female | 7 | 8 | 5 | 8 | 3 | ||||||||
| 7 | Beth | 4B | Female | 3 | 3 | 3 | 3 | 3 | ||||||||
| 8 | Tom | 4B | Male | 3 | 5 | 2 | 2 | 2 | ||||||||
| 9 | Simone | 4C | Female | 2 | 2 | 7 | 2 | 8 | ||||||||
| 10 | Colin | 4C | Male | 5 | 3 | 5 | 5 | 3 | ||||||||
| 11 | Stephanie | 4C | Female | 6 | 6 | 6 | 6 | 6 | ||||||||
Grading | ||||||||||||||||
| Grading.xlsx | |||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | |||
| 14 | Name | Class | Gender | Art & Design | A&D Grade | Business | Bus Grade | Chemistry | Chem Grade | Dance | Dance Grade | Design | Des Grade | Drama | Drama Grade | English | Eng Grade | French | French Grade | Geography | Geo Grade | Graphics | Graph Grade | History | Hist Grade | ||
| 15 | George | 4A | Male | 4 | 5 | 2 | 6 | 6 | 7 | 4 | 5 | 8 | 7 | 2 | |||||||||||||
| 16 | Larry | 4A | Male | 4 | 3 | 3 | 5 | 4 | 3 | 6 | 3 | 6 | 3 | ||||||||||||||
| 17 | Divya | 4A | Female | 5 | 7 | 1 | 6 | 1 | 1 | 8 | 4 | 5 | 7 | 7 | 4 | ||||||||||||
| 18 | Urvesh | 4A | Male | 7 | 5 | 4 | 5 | 7 | 4 | 7 | 7 | 2 | 6 | ||||||||||||||
| 19 | Suzie | 4B | Female | 7 | 8 | 6 | 5 | 8 | 4 | 5 | 3 | 7 | 7 | ||||||||||||||
| 20 | Beth | 4B | Female | 6 | 3 | 6 | 3 | 4 | 3 | 4 | 3 | 3 | 7 | ||||||||||||||
| 21 | Tom | 4B | Male | 3 | 5 | 6 | 2 | 7 | 2 | 4 | 4 | 2 | 4 | 7 | |||||||||||||
| 22 | Simone | 4C | Female | 2 | 2 | 6 | 7 | 5 | 7 | 2 | 8 | 6 | |||||||||||||||
| 23 | Colin | 4C | Male | 6 | 5 | 3 | 6 | 5 | 5 | 3 | 4 | 5 | 5 | 7 | 6 | ||||||||||||
| 24 | Stephanie | 4C | Female | 6 | 6 | 6 | 6 | 6 | 7 | ||||||||||||||||||
Grading | |||||||||||||||||||||||||||
