Filter rows that meet criteria

[GMC The Macro Man]

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.
What I want is to filter out (on a separate sheet) any pupil who has a 7 or 8 listed in their expected results so this can be passed to department heads to give the pupil assistance.
The pupils will only sit a few of the subjects available.
Some pupils could potentially have BOTH 7 and an 8 in their scores
I have only given a small cut out of the data as there is over 1000 pupils and 20 something subjects
Is it possible to pull any row of data that contains a 7 or an 8 (or both) to a separate sheet?

Thanks
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

 

[Fluff]

How about

Fluff.xlsm
	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	0	2	0	6	0	7	0	8	0	2
3	Divya	4A	Female	0	0	1	1	1	0	8	0	0	7	0
4	Suzie	4B	Female	7	8	0	5	0	8	0	0	3	0	0
5	Simone	4C	Female	2	2	0	0	0	0	7	2	0	8	0
Sheet2

Cell Formulas
Range	Formula
A2:N5	A2	=FILTER(Grading!A2:N11,MMULT(--(Grading!D2:N11>7),SEQUENCE(COLUMNS(Grading!D1:N1),,,0)))
Dynamic array formulas.

 

[GMC The Macro Man]

How about

Fluff.xlsm
	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	0	2	0	6	0	7	0	8	0	2
3	Divya	4A	Female	0	0	1	1	1	0	8	0	0	7	0
4	Suzie	4B	Female	7	8	0	5	0	8	0	0	3	0	0
5	Simone	4C	Female	2	2	0	0	0	0	7	2	0	8	0
Sheet2

Cell Formulas
Range	Formula
A2:N5	A2	=FILTER(Grading!A2:N11,MMULT(--(Grading!D2:N11>7),SEQUENCE(COLUMNS(Grading!D1:N1),,,0)))
Dynamic array formulas.

Thank you Fluff, I didn't know about MMULT. I suppose every day is a school day

Appreciate it as always
George

 

[Fluff]

You're welcome & thanks for the feedback.

 

[GMC The Macro Man]

Morning Fluff, Quick question if I may,
On the above Table, if the value was blank then the formula is now returning a "0". what formula do I need to add on to yours so it displays a BLANK instead of the ZERO
Thanks
George

 

[Fluff]

How about

=LET(f,FILTER(Grading!A2:N11,MMULT(--(Grading!D2:N11>7),SEQUENCE(COLUMNS(Grading!D1:N1),,,0))),IF(f="","",f))

 

[GMC The Macro Man]

Cheers fluff, I tried to do this yesterday but i forgot the "equals" sign in the IF part..
Have a good weekend bud
George