Filter columns with 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.
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

 

[Fluff]

How about

=LET(d,CHOOSECOLS(Grading!D2:Y11,SEQUENCE(,11,,2)),f,FILTER(HSTACK(Grading!A2:C11,d),MMULT(--(d>=7),SEQUENCE(COLUMNS(d),,,0))),IF(f="","",f))

 

[GMC The Macro Man]

Thank you Fluff.
I'm using Office Pro Plus 2021 and it seems it does not like CHOOSECOLS or HSTACK in my formulation (I'm sure I have used CHOOSECOLS before).
I'm getting the error #NAME? and when I step through it it highlights these 2.
Is there another way to do this calculation or to force this one through.
TIA
George

 

[Fluff]

Oops, how about

=LET(r,SEQUENCE(ROWS(Grading!D2:Y11)),d,INDEX(Grading!D2:Y11,r,SEQUENCE(,11,,2)),c,COLUMNS(d),s,SEQUENCE(,c+3),f,FILTER(IF(s<=3,Grading!A2:C11,INDEX(d,r,s-3)),MMULT(--(d>=7),SEQUENCE(c,,,0))),IF(f=0,"",f))

 

[GMC The Macro Man]

Oops, how about

=LET(r,SEQUENCE(ROWS(Grading!D2:Y11)),d,INDEX(Grading!D2:Y11,r,SEQUENCE(,11,,2)),c,COLUMNS(d),s,SEQUENCE(,c+3),f,FILTER(IF(s<=3,Grading!A2:C11,INDEX(d,r,s-3)),MMULT(--(d>=7),SEQUENCE(c,,,0))),IF(f=0,"",f))

Hi Fluff

I tried to make your Formula above work in my spreadsheet but I couldn't get it to work properly, but this is not your fault. Its mine because in the sample spreadsheet above, I only gave a minimal selection of data (for GDPR reasons) so when I tried to implement this in to the larger working sheet, even though I thought it would just be a simple change to the formula, I couldn't get it to work so if you would be so kind, could you have a look at the data sheet that I am working on.
There are 27 subjects from Accounting to Tourism. Each subject has 4 columns, Nat Level, Teacher, Tracking Grade and Working Grade.

My request is to look at the Working grade column for every subject and if it holds a 7 or above then to FILTER that row in to a different sheet. Other columns may have a number 7 or above so its crucial to just focus the filter on the Working Grade columns.
The size of this sheet is from cell A2-DH600 (the xL2BB only allows a limited number of cells to copy over)

Is there any way you can help
Thanks as always

School 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
1	Unique ID	Full Name	Reg Class	Accounting Nat Level	Accounting Teacher	Acc Tracking Grade	Acc Working Grade	Art & Design Nat Level	Art & Design Teacher	A&D Tracking Grade	A&D Working Grade	Automotive Engineering Nat Level	Automative Teacher	Auto Tracking Grade	Auto Working Grade	Biology Nat Level	Biology Teacher	Biology Tracking Grade	Biology Working Grade	Business Nat Level	Business Teacher	Business Tracking Grade	Bus Working Grade
2	141266535	Aaron Anthony Dempsey	4A	Nat 4	Scott Singer	6	6													Nat 5	Marie Claire McCallum	6	6
3	141264788	Aaron Lucas	4A
4	141265792	Abby Kelly	4A	Nat 4	Scott Singer	6	6													Nat 5	Abby Murray	7	7
5	141254774	Aiden Thom	4A																	Nat 5	Joseph Brannan	4	4
6	141380699	Anthony Hamilton	4A																	Nat 5	Marie Claire McCallum	7	7
7	141254456	Cailin Marie Gilliland	4A					Nat 5	Ruth Mercedes Ramsay	1	3					Nat 5	Scott Singer	2	3
8	141265865	Caitlin Ann Tierney	4A					Nat 5	Ciara Barclay	0	0					Nat 5	Scott Singer	6	7	Nat 5	Abby Murray	4	4
9	141266837	Callum Donaldson	4A														Scott Singer	6	9	Nat 5	Marie Claire McCallum	7	7
10	141283138	Callum Quinn	4A	Nat 4	Scott Singer	6	7									Nat 5
11	141254375	George Brown	4A													Nat 5	Amanda Lynas	3	5
12	140904546	Harvey Forbes	4A													Nat 5	Amanda Lynas	6	9	Nat 5	Marie Claire McCallum	7	7
13	181420634	Hope Eniayeye Joseph	4A													Nat 5	Hannah Coakley	7	8
14	141282840	Joe Carrigan	4A
15	141355821	Jorgia Cassidy	4A	Nat 4	Scott Singer	6	6	Nat 5	Ciara Barclay	0	0
16	141265482	Joshua Gallagher	4A																	Nat 5	Abby Murray	1	1
17	141254677	Kayla Elizabeth Paterson	4A	Nat 4	Scott Singer	6	7	Nat 5	Ciara Barclay	0	0
18	141274430	Keira Louise McCann	4A
19	141254588	Kieran McFarlane	4A													Nat 5	Anne Sulko	7	8
20	141265652	Kitty Marcella Mary Murphy O'Neill	4A	Nat 4	Anne Sulko	6	6
21	141265490	Lewis John King	4A	Nat 4	Anne Sulko	6	6
22	141576348	Lucy Nolan	4A													Nat 5	Scott Singer	5	6	Nat 5	Joseph Brannan	2	2
23	141254669	Matthew O'Connor	4A																	Nat 5	Abby Murray	4	4
24	141254685	Paige Purdue	4A					Nat 5	Ciara Barclay	0	0					Nat 5	Scott Singer	6	7	Nat 5	Joseph Brannan	4	4
25	141363263	Patrick Joseph Curtis	4A	Nat 4	Scott Singer	6	6
26	141254502	Rhys Jackson	4A																	Nat 5	Abby Murray	5	5
27	141266608	Ruaridh Gilchrist	4A																	Nat 5	Marie Claire McCallum	5	5
28	141264567	Zak Charlie Chapman	4A	Nat 4	Scott Singer	6	7
29	141254626	Zoe Anna Maguire	4A													Nat 5	Hannah Coakley	5	6	Nat 5	Abby Murray	1	1
30	141266756	Adam Mason	4B
31	141254448	Aimee Gillies	4B													Nat 5	Anne Sulko	2	3
32	141282778	Bobby David McDonald	4B	Nat 4	Anne Sulko	6	6
33	141446266	Cole Quinn	4B													Nat 5	Scott Singer	6	8
34	141254413	Corry Fagan	4B	Nat 4	Scott Singer	6	6
35	141282921	Eamonn Peter Quinn	4B																	Nat 5	Marie Claire McCallum	3	3
36	141267655	Elizabeth Morrison	4B													Nat 5	Amanda Lynas	3	5
37	141254510	Ellie Mairead Jones	4B													Nat 5	Anne Sulko	6	7
38	141254707	Emily Marie Reilly	4B					Nat 5	Georgia Galloway	6	6					Nat 5	Scott Singer	6	8
39	141254812	Finn O'Brien	4B
40	141266500	Fraser Crockett	4B
Sheet1

 

[Fluff]

Try

=LET(r,SEQUENCE(ROWS(Grading!D2:DH600)),d,INDEX(Grading!D2:DH600,r,SEQUENCE(,27,4,4)),c,COLUMNS(d),s,SEQUENCE(,c+3),f,FILTER(IF(s<=3,Grading!A2:C600,INDEX(d,r,s-3)),MMULT(--(d>=7),SEQUENCE(c,,,0))),IF(f=0,"",f))

 

[GMC The Macro Man]

Try

=LET(r,SEQUENCE(ROWS(Grading!D2:DH600)),d,INDEX(Grading!D2:DH600,r,SEQUENCE(,27,4,4)),c,COLUMNS(d),s,SEQUENCE(,c+3),f,FILTER(IF(s<=3,Grading!A2:C600,INDEX(d,r,s-3)),MMULT(--(d>=7),SEQUENCE(c,,,0))),IF(f=0,"",f))

Thanks Fluff
I.m just heading for the train and I will get this checked tonight.
Appreciate as always

 

[Fluff]

Ok

 

[GMC The Macro Man]

Ok

H Fluff

I ran this and it does filter out the data if it contains a 7 or above in any of the "Working Grade" columns so that's great but it actually squeezies all the data into the first 5 or 6 columns so from column AD, there is nothing. See the Mini Sheet.

Would it be possible to change it slightly so that it pulls over all the data in the row if it contains a 7or above in any of the working grades, so not to return just the Working Grade only but it would have the Nat Level, Teachers Name, Tracking Grade and Working Grade. As the image below
Cheers

School 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	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN
1	Unique ID	Full Name	Reg Class	Accounting Nat Level	Accounting Teacher	Acc Tracking Grade	Acc Working Grade	Art & Design Nat Level	Art & Design Teacher	A&D Tracking Grade	A&D Working Grade	Automotive Engineering Nat Level	Automative Teacher	Auto Tracking Grade	Auto Working Grade	Biology Nat Level	Biology Teacher	Biology Tracking Grade	Biology Working Grade	Business Nat Level	Business Teacher	Business Tracking Grade	Bus Working Grade	Chemistry Nat Level	Chemistry Teacher	Chemistry Tracking Grade	Chemistry Working Grade	Design Nat Level	Design Teacher	Design Tracking Grade	Design Working Grade	Drama Nat Level	Drama Teacher	Drama Tracking Grade	Drama Working Grade	English Nat Level	English Teacher	English Tracking Grade	English Working Grade	French Nat Level
2	141266535	Aaron Anthony Dempsey	4A	6				6				6					6			7	7		6
3	141264788	Aaron Lucas	4A									7			6		7			6	7		5		7
4	141265792	Abby Kelly	4A	6				7				6			7	8				6	8
5	141254774	Aiden Thom	4A					4	8			6					7			7			5			5
6	141380699	Anthony Hamilton	4A					7	9				8				8			8						7
7	141265865	Caitlin Ann Tierney	4A				7	4				6					4			7									5
8	141266837	Callum Donaldson	4A				9	7				6								6			5						8
9	141283138	Callum Quinn	4A	7					9			7					9			9		9				9
10	141254375	George Brown	4A				5					7		8	6		8			6			7
11	140904546	Harvey Forbes	4A				9	7				6	7		7		6			7
12	181420634	Hope Eniayeye Joseph	4A				8				7	7					7			7		8							6
13	141282840	Joe Carrigan	4A									7								9					7	9
14	141355821	Jorgia Cassidy	4A	6												5	6			8			7
15	141254677	Kayla Elizabeth Paterson	4A	7									8				7			7
16	141274430	Keira Louise McCann	4A																	9
17	141254588	Kieran McFarlane	4A				8		8			6			6					3	6		6
18	141265652	Kitty Marcella Mary Murphy O'Neill	4A	6								6				8				6	7	8							8
19	141265490	Lewis John King	4A	6						7		7					8			7			7			9
20	141576348	Lucy Nolan	4A				6	2				5	7						4	4	5
21	141254669	Matthew O'Connor	4A					4				6					4			7	4		5		4
22	141254685	Paige Purdue	4A				7	4				4	7							4	5
23	141254502	Rhys Jackson	4A					5				6			6	7	4			6					7
24	141266608	Ruaridh Gilchrist	4A					5	8			5	7							7	5				2
25	141264567	Zak Charlie Chapman	4A	7								7				7	7			6			6						8
26	141254626	Zoe Anna Maguire	4A				6	1				6	7				6			7	6
27	141266756	Adam Mason	4B						4			3					4			5	5				4				7
28	141282778	Bobby David McDonald	4B	6								6		7	6		7			8		6
29	141446266	Cole Quinn	4B				8			5					5		6			6			5
Sheet2

Cell Formulas
Range	Formula
A2:AD180	A2	=LET(r,SEQUENCE(ROWS(Grading!D2:DH600)),d,INDEX(Grading!D2:DH600,r,SEQUENCE(,27,4,4)),c,COLUMNS(d),s,SEQUENCE(,c+3),f,FILTER(IF(s<=3,Grading!A2:C600,INDEX(d,r,s-3)),MMULT(--(d>=7),SEQUENCE(c,,,0))),IF(f=0,"",f))
Dynamic array formulas.

 

[GMC The Macro Man]

## Update on the above ##
"squeezies all the data into the first 5 or 6 columns so from column AD, there is nothing."
Having looked at this in more detail i can see thats because I have still kept ALL the column headers in my output page and NOT filtered out JUST the Working Grade columns, so your formula is PERFECT in my eyes....
I will present this to my client tomorrow and explain this is a cleaner output but I know what they are like and I;m positive the will ask for everything to be returned.
So see my question above about pulling over all the data - would this be possible please?