Filter out the blanks and return cells with values only without VBA

[GMC The Macro Man]

Hello team

I have a data sheet with student details and the scores for the subjects they chose. I need to filter the scores by subject on to different Tabs, however I need to filter out the blanks
In the example here the PupilData Tab details the User ID, Name, Year, Class and Subjects chosen with results
In the next 2 tabs (Design and Spanish) I have just cut n pasted for the purpose of this exercise the relevant data,
What I want to have is a formula that says - only return the rows that have data in the chosen subject column (col E). The 2 final tabs (Design_Yes and Spanish_Yes) are what I am looking for the output to be.
I can Use =FILTER(CHOOSE.. and chose the columns to return but this won't cut out the blanks.
Can any one assist with this.

Thanks as always

Mr Excel.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
1	ID	Known As	Year Group	Class	Art & Design	Business	Chemistry	Dance	Design	Drama	English	French	Geography	Graphics	History	Human Biology	Italian	Maths	Modern Studies	Music	PE (SQA)	Physics	RE (SQA)	Spanish
2	1001234	Cha	S4	4A	1				7		4			7				6						3
3	1001235	Sau	S4	4A				7		1	7		5					5	8	3				2
4	1001236	Mill	S4	4B							3						6	6		5			1
5	1001237	Kat	S4	4B					1		5			8	2		8	5		7		2
6	1001238	Im	S4	4C	3					3	5		7				1	5	1
7	1001239	So	S4	4C							6							6					3
8	1001240	Se	S4	4D			7	8			6							5				4		6
9	1001241	Nic	S4	4D	5				3		3			2				6
10	1001242	Luc	S4	4A					5	5	5		8	4	4			4	3				5	8
11	1001243	Ja	S5	5A		8	8			7	2		2			6		7	5	2
12	1001244	Jo	S5	5B		2					4	7						3				6		2
13	1001245	Da	S5	5B	7			2			5	8			6			5				8		4
14	1001246	Ra	S5	5C	8				7		6			6				5		4
15	1001247	Li	S5	5C			2			8	4		4			8	3	6	7		4
16	1001248	Pi	S5	5D		4	4				5					1		6						6
17	1001249	Ye	S5	5D				4	8		4	2		8	8			3				2
18	1001250	Yang	S6	6A	2					2	5		6		1		5	5	2	6	6			1
19	1001251	Tom	S6	6A		6					5							2		8
20	1001252	Liz	S6	6B			6		2		5	4		1		3		4				4	7	8
21	1001253	George	S6	6B	4					4	7		8					5	4					2
PupilData

Mr Excel.xlsx
	A	B	C	D	E
1	ID	Known As	Year Group	Class	Design
2	1001234	Cha	S4	4A	7
3	1001235	Sau	S4	4A
4	1001236	Mill	S4	4B
5	1001237	Kat	S4	4B	1
6	1001238	Im	S4	4C
7	1001239	So	S4	4C
8	1001240	Se	S4	4D
9	1001241	Nic	S4	4D	3
10	1001242	Luc	S4	4A	5
11	1001243	Ja	S5	5A
12	1001244	Jo	S5	5B
13	1001245	Da	S5	5B
14	1001246	Ra	S5	5C	7
15	1001247	Li	S5	5C
16	1001248	Pi	S5	5D
17	1001249	Ye	S5	5D	8
18	1001250	Yang	S6	6A
19	1001251	Tom	S6	6A
20	1001252	Liz	S6	6B	2
21	1001253	George	S6	6B
Design

Mr Excel.xlsx
	A	B	C	D	E
1	ID	Known As	Year Group	Class	Spanish
2	1001234	Cha	S4	4A	3
3	1001235	Sau	S4	4A	2
4	1001236	Mill	S4	4B
5	1001237	Kat	S4	4B
6	1001238	Im	S4	4C
7	1001239	So	S4	4C
8	1001240	Se	S4	4D	6
9	1001241	Nic	S4	4D
10	1001242	Luc	S4	4A	8
11	1001243	Ja	S5	5A
12	1001244	Jo	S5	5B	2
13	1001245	Da	S5	5B	4
14	1001246	Ra	S5	5C
15	1001247	Li	S5	5C
16	1001248	Pi	S5	5D	6
17	1001249	Ye	S5	5D
18	1001250	Yang	S6	6A	1
19	1001251	Tom	S6	6A
20	1001252	Liz	S6	6B	8
21	1001253	George	S6	6B	2
Spanish

Mr Excel.xlsx
	A	B	C	D	E
1	ID	Known As	Year Group	Class	Design
2	1001234	Cha	S4	4A	7
3	1001237	Kat	S4	4B	1
4	1001241	Nic	S4	4D	3
5	1001242	Luc	S4	4A	5
6	1001246	Ra	S5	5C	7
7	1001249	Ye	S5	5D	8
8	1001252	Liz	S6	6B	2
Design_Yes

Mr Excel.xlsx
	A	B	C	D	E
1	ID	Known As	Year Group	Class	Spanish
2	1001234	Cha	S4	4A	3
3	1001235	Sau	S4	4A	2
4	1001240	Se	S4	4D	6
5	1001242	Luc	S4	4A	8
6	1001244	Jo	S5	5B	2
7	1001245	Da	S5	5B	4
8	1001248	Pi	S5	5D	6
9	1001250	Yang	S6	6A	1
10	1001252	Liz	S6	6B	8
11	1001253	George	S6	6B	2
Spanish_Yes

 

[6StringJazzer]

Here is the solution for Design. Same solution applies to other subjects. I don't know what FILTER formula you were trying to use, but this is done with two FILTER formulas.

$scratch.xlsm
	A	B	C	D	E
1	ID	Known As	Year Group	Class	Design
2	1001234	Cha	S4	4A	7
3	1001237	Kat	S4	4B	1
4	1001241	Nic	S4	4D	3
5	1001242	Luc	S4	4A	5
6	1001246	Ra	S5	5C	7
7	1001249	Ye	S5	5D	8
8	1001252	Liz	S6	6B	2
Design_Yes

Cell Formulas
Range	Formula
A1:D8	A1	=FILTER(PupilData!$A:$D,PupilData!$I:$I<>"")
E1:E8	E1	=FILTER(PupilData!$I:$I,PupilData!$I:$I<>"")
Dynamic array formulas.

 

[duggie33]

I came up with something similar to Jeff's solution. In Z1, if you select the class from the dropdown data validation, the filter should update just below it.

Book1.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
1	ID	Known As	Year Group	Class	Art & Design	Business	Chemistry	Dance	Design	Drama	English	French	Geography	Graphics	History	Human Biology	Italian	Maths	Modern Studies	Music	PE (SQA)	Physics	RE (SQA)	Spanish		History
2	1001234	Cha	S4	4A	1				7		4			7				6						3
3	1001235	Sau	S4	4A				7		1	7		5					5	8	3				2		ID	Known As	Year Group	Class	History
4	1001236	Mill	S4	4B							3						6	6		5			1			1001237	Kat	S4	4B	2
5	1001237	Kat	S4	4B					1		5			8	2		8	5		7		2				1001242	Luc	S4	4A	4
6	1001238	Im	S4	4C	3					3	5		7				1	5	1							1001245	Da	S5	5B	6
7	1001239	So	S4	4C							6							6					3			1001249	Ye	S5	5D	8
8	1001240	Se	S4	4D			7	8			6							5				4		6		1001250	Yang	S6	6A	1
9	1001241	Nic	S4	4D	5				3		3			2				6
10	1001242	Luc	S4	4A					5	5	5		8	4	4			4	3				5	8
11	1001243	Ja	S5	5A		8	8			7	2		2			6		7	5	2
12	1001244	Jo	S5	5B		2					4	7						3				6		2
13	1001245	Da	S5	5B	7			2			5	8			6			5				8		4
14	1001246	Ra	S5	5C	8				7		6			6				5		4
15	1001247	Li	S5	5C			2			8	4		4			8	3	6	7		4
16	1001248	Pi	S5	5D		4	4				5					1		6						6
17	1001249	Ye	S5	5D				4	8		4	2		8	8			3				2
18	1001250	Yang	S6	6A	2					2	5		6		1		5	5	2	6	6			1
19	1001251	Tom	S6	6A		6					5							2		8
20	1001252	Liz	S6	6B			6		2		5	4		1		3		4				4	7	8
21	1001253	George	S6	6B	4					4	7		8					5	4					2
Sheet2

Cell Formulas
Range	Formula
Z3:AD8	Z3	=LET(rng,A1:X21,filt_1,FILTER(rng,INDEX(rng,,MATCH(Z1,INDEX(rng,1,),))<>""),HSTACK(INDEX(filt_1,,1),INDEX(filt_1,,2),INDEX(filt_1,,3),INDEX(filt_1,,4),INDEX(filt_1,,MATCH(Z1,INDEX(filt_1,1,),0))))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
Z1	List	=$E$1:$X$1

Doug

 

[Peter_SSs]

I can Use =FILTER(CHOOSE.. and chose the columns to return but this won't cut out the blanks.

Are you saying that you would use CHOOSECOLS? If so, that would indicate that you are not using Excel 2021 as indicated in your profile. If you still need further help after what has been offered, can you please confirm the version(s) this needs to work in?

Below I have suggested formulas that can be used exactly the same on each 'subject' worksheet. The formula in A1 if you have the available functions in Excel 365, the formula in A11 if it has to work in Excel 2021. Check the Table name. I have hidden a few columns in this 'Pupil Data' sheet to keep the mini sheet a bit smaller for the forum.

GMC The Macro Man.xlsm
	A	B	C	D				H	I	J					O	P	Q						W	X
1	ID	Known As	Year Group	Class				Dance	Design	Drama					History	Human Biology	Italian						RE (SQA)	Spanish
2	1001234	Cha	S4	4A					7															3
3	1001235	Sau	S4	4A				7		1														2
4	1001236	Mill	S4	4B													6						1
5	1001237	Kat	S4	4B					1						2		8
6	1001238	Im	S4	4C						3							1
7	1001239	So	S4	4C																			3
8	1001240	Se	S4	4D				8																6
9	1001241	Nic	S4	4D					3
10	1001242	Luc	S4	4A					5	5					4								5	8
11	1001243	Ja	S5	5A						7						6
12	1001244	Jo	S5	5B																				2
13	1001245	Da	S5	5B				2							6									4
14	1001246	Ra	S5	5C					7
15	1001247	Li	S5	5C						8						8	3
16	1001248	Pi	S5	5D												1								6
17	1001249	Ye	S5	5D				4	8						8
18	1001250	Yang	S6	6A						2					1		5							1
19	1001251	Tom	S6	6A
20	1001252	Liz	S6	6B					2							3							7	8
21	1001253	George	S6	6B						4														2
Pupil Data

GMC The Macro Man.xlsm
	A	B	C	D	E
1	ID	Known As	Year Group	Class	Design
2	1001234	Cha	S4	4A	7
3	1001237	Kat	S4	4B	1
4	1001241	Nic	S4	4D	3
5	1001242	Luc	S4	4A	5
6	1001246	Ra	S5	5C	7
7	1001249	Ye	S5	5D	8
8	1001252	Liz	S6	6B	2
9
10
11	ID	Known As	Year Group	Class	Design
12	1001234	Cha	S4	4A	7
13	1001237	Kat	S4	4B	1
14	1001241	Nic	S4	4D	3
15	1001242	Luc	S4	4A	5
16	1001246	Ra	S5	5C	7
17	1001249	Ye	S5	5D	8
18	1001252	Liz	S6	6B	2
19
Design

Cell Formulas
Range	Formula
A1:E8	A1	=LET(d,CHOOSECOLS(PupilData[#All],1,2,3,4,MATCH(TEXTAFTER(CELL("filename",A1),"]"),PupilData[#Headers],0)),FILTER(d,TAKE(d,,-1)<>""))
A11:E18	A11	=LET(fn,CELL("filename",A11),d,FILTER(PupilData[#All],ISNUMBER(SEARCH("|"&PupilData[#Headers]&"|","|ID|Known As|Year Group|Class|"&REPLACE(fn,1,FIND("]",fn),"")&"|"))),FILTER(d,INDEX(d,0,5)<>""))
Dynamic array formulas.

GMC The Macro Man.xlsm
	A	B	C	D	E
1	ID	Known As	Year Group	Class	Human Biology
2	1001243	Ja	S5	5A	6
3	1001247	Li	S5	5C	8
4	1001248	Pi	S5	5D	1
5	1001252	Liz	S6	6B	3
6
7
8
9
10
11	ID	Known As	Year Group	Class	Human Biology
12	1001243	Ja	S5	5A	6
13	1001247	Li	S5	5C	8
14	1001248	Pi	S5	5D	1
15	1001252	Liz	S6	6B	3
16
Human Biology

Cell Formulas
Range	Formula
A1:E5	A1	=LET(d,CHOOSECOLS(PupilData[#All],1,2,3,4,MATCH(TEXTAFTER(CELL("filename",A1),"]"),PupilData[#Headers],0)),FILTER(d,TAKE(d,,-1)<>""))
A11:E15	A11	=LET(fn,CELL("filename",A11),d,FILTER(PupilData[#All],ISNUMBER(SEARCH("|"&PupilData[#Headers]&"|","|ID|Known As|Year Group|Class|"&REPLACE(fn,1,FIND("]",fn),"")&"|"))),FILTER(d,INDEX(d,0,5)<>""))
Dynamic array formulas.

 

[GMC The Macro Man]

Here is the solution for Design. Same solution applies to other subjects. I don't know what FILTER formula you were trying to use, but this is done with two FILTER formulas.

$scratch.xlsm
	A	B	C	D	E
1	ID	Known As	Year Group	Class	Design
2	1001234	Cha	S4	4A	7
3	1001237	Kat	S4	4B	1
4	1001241	Nic	S4	4D	3
5	1001242	Luc	S4	4A	5
6	1001246	Ra	S5	5C	7
7	1001249	Ye	S5	5D	8
8	1001252	Liz	S6	6B	2
Design_Yes

Cell Formulas
Range	Formula
A1:D8	A1	=FILTER(PupilData!$A:$D,PupilData!$I:$I<>"")
E1:E8	E1	=FILTER(PupilData!$I:$I,PupilData!$I:$I<>"")
Dynamic array formulas.

Hi Jeff, Many thanks for your help here on this and my apologies for the lateness on the response as I'm just back from holiday.

 

[GMC The Macro Man]

I came up with something similar to Jeff's solution. In Z1, if you select the class from the dropdown data validation, the filter should update just below it.

Book1.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
1	ID	Known As	Year Group	Class	Art & Design	Business	Chemistry	Dance	Design	Drama	English	French	Geography	Graphics	History	Human Biology	Italian	Maths	Modern Studies	Music	PE (SQA)	Physics	RE (SQA)	Spanish		History
2	1001234	Cha	S4	4A	1				7		4			7				6						3
3	1001235	Sau	S4	4A				7		1	7		5					5	8	3				2		ID	Known As	Year Group	Class	History
4	1001236	Mill	S4	4B							3						6	6		5			1			1001237	Kat	S4	4B	2
5	1001237	Kat	S4	4B					1		5			8	2		8	5		7		2				1001242	Luc	S4	4A	4
6	1001238	Im	S4	4C	3					3	5		7				1	5	1							1001245	Da	S5	5B	6
7	1001239	So	S4	4C							6							6					3			1001249	Ye	S5	5D	8
8	1001240	Se	S4	4D			7	8			6							5				4		6		1001250	Yang	S6	6A	1
9	1001241	Nic	S4	4D	5				3		3			2				6
10	1001242	Luc	S4	4A					5	5	5		8	4	4			4	3				5	8
11	1001243	Ja	S5	5A		8	8			7	2		2			6		7	5	2
12	1001244	Jo	S5	5B		2					4	7						3				6		2
13	1001245	Da	S5	5B	7			2			5	8			6			5				8		4
14	1001246	Ra	S5	5C	8				7		6			6				5		4
15	1001247	Li	S5	5C			2			8	4		4			8	3	6	7		4
16	1001248	Pi	S5	5D		4	4				5					1		6						6
17	1001249	Ye	S5	5D				4	8		4	2		8	8			3				2
18	1001250	Yang	S6	6A	2					2	5		6		1		5	5	2	6	6			1
19	1001251	Tom	S6	6A		6					5							2		8
20	1001252	Liz	S6	6B			6		2		5	4		1		3		4				4	7	8
21	1001253	George	S6	6B	4					4	7		8					5	4					2
Sheet2

Cell Formulas
Range	Formula
Z3:AD8	Z3	=LET(rng,A1:X21,filt_1,FILTER(rng,INDEX(rng,,MATCH(Z1,INDEX(rng,1,),))<>""),HSTACK(INDEX(filt_1,,1),INDEX(filt_1,,2),INDEX(filt_1,,3),INDEX(filt_1,,4),INDEX(filt_1,,MATCH(Z1,INDEX(filt_1,1,),0))))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
Z1	List	=$E$1:$X$1

Doug

Thanks Doug for your input, however I had already used Jeff's solution to complete the report but I will certainly hold on to your idea as well. I'm just back from holiday and that's why it has taken so long to respond. George

 

[GMC The Macro Man]

Are you saying that you would use CHOOSECOLS? If so, that would indicate that you are not using Excel 2021 as indicated in your profile. If you still need further help after what has been offered, can you please confirm the version(s) this needs to work in?

Below I have suggested formulas that can be used exactly the same on each 'subject' worksheet. The formula in A1 if you have the available functions in Excel 365, the formula in A11 if it has to work in Excel 2021. Check the Table name. I have hidden a few columns in this 'Pupil Data' sheet to keep the mini sheet a bit smaller for the forum.

GMC The Macro Man.xlsm
	A	B	C	D				H	I	J					O	P	Q						W	X
1	ID	Known As	Year Group	Class				Dance	Design	Drama					History	Human Biology	Italian						RE (SQA)	Spanish
2	1001234	Cha	S4	4A					7															3
3	1001235	Sau	S4	4A				7		1														2
4	1001236	Mill	S4	4B													6						1
5	1001237	Kat	S4	4B					1						2		8
6	1001238	Im	S4	4C						3							1
7	1001239	So	S4	4C																			3
8	1001240	Se	S4	4D				8																6
9	1001241	Nic	S4	4D					3
10	1001242	Luc	S4	4A					5	5					4								5	8
11	1001243	Ja	S5	5A						7						6
12	1001244	Jo	S5	5B																				2
13	1001245	Da	S5	5B				2							6									4
14	1001246	Ra	S5	5C					7
15	1001247	Li	S5	5C						8						8	3
16	1001248	Pi	S5	5D												1								6
17	1001249	Ye	S5	5D				4	8						8
18	1001250	Yang	S6	6A						2					1		5							1
19	1001251	Tom	S6	6A
20	1001252	Liz	S6	6B					2							3							7	8
21	1001253	George	S6	6B						4														2
Pupil Data

GMC The Macro Man.xlsm
	A	B	C	D	E
1	ID	Known As	Year Group	Class	Design
2	1001234	Cha	S4	4A	7
3	1001237	Kat	S4	4B	1
4	1001241	Nic	S4	4D	3
5	1001242	Luc	S4	4A	5
6	1001246	Ra	S5	5C	7
7	1001249	Ye	S5	5D	8
8	1001252	Liz	S6	6B	2
9
10
11	ID	Known As	Year Group	Class	Design
12	1001234	Cha	S4	4A	7
13	1001237	Kat	S4	4B	1
14	1001241	Nic	S4	4D	3
15	1001242	Luc	S4	4A	5
16	1001246	Ra	S5	5C	7
17	1001249	Ye	S5	5D	8
18	1001252	Liz	S6	6B	2
19
Design

Cell Formulas
Range	Formula
A1:E8	A1	=LET(d,CHOOSECOLS(PupilData[#All],1,2,3,4,MATCH(TEXTAFTER(CELL("filename",A1),"]"),PupilData[#Headers],0)),FILTER(d,TAKE(d,,-1)<>""))
A11:E18	A11	=LET(fn,CELL("filename",A11),d,FILTER(PupilData[#All],ISNUMBER(SEARCH("|"&PupilData[#Headers]&"|","|ID|Known As|Year Group|Class|"&REPLACE(fn,1,FIND("]",fn),"")&"|"))),FILTER(d,INDEX(d,0,5)<>""))
Dynamic array formulas.

GMC The Macro Man.xlsm
	A	B	C	D	E
1	ID	Known As	Year Group	Class	Human Biology
2	1001243	Ja	S5	5A	6
3	1001247	Li	S5	5C	8
4	1001248	Pi	S5	5D	1
5	1001252	Liz	S6	6B	3
6
7
8
9
10
11	ID	Known As	Year Group	Class	Human Biology
12	1001243	Ja	S5	5A	6
13	1001247	Li	S5	5C	8
14	1001248	Pi	S5	5D	1
15	1001252	Liz	S6	6B	3
16
Human Biology

Cell Formulas
Range	Formula
A1:E5	A1	=LET(d,CHOOSECOLS(PupilData[#All],1,2,3,4,MATCH(TEXTAFTER(CELL("filename",A1),"]"),PupilData[#Headers],0)),FILTER(d,TAKE(d,,-1)<>""))
A11:E15	A11	=LET(fn,CELL("filename",A11),d,FILTER(PupilData[#All],ISNUMBER(SEARCH("|"&PupilData[#Headers]&"|","|ID|Known As|Year Group|Class|"&REPLACE(fn,1,FIND("]",fn),"")&"|"))),FILTER(d,INDEX(d,0,5)<>""))
Dynamic array formulas.

Hi Peter, thanks for the reply and I am using Office Pro Plus 2021 and I know that I have used the =Filter(Choosecols... function as I'm not great on the LET formulas. As to which version I need to use this on I'm positive my end users will be on 365 so I will certainly look at your solutions. I'm just back from a late summer holiday break and thats why it has taken this long to respond. Thanks again, George