Filter horizontal ignore empty cells and zero values

[ExcelNewbie2020]

i have this table

Name-List-for-Practice.xlsm
	A	B	C	D	E	F
1
2	Name	Dept	PEN	PAPER	PENCIL	BOOK
3	Name1	A	2			8
4	Name2	B	0	2		4
5	Name3	C	2			1
6	Name4	D	2	0	3	1
7
8
9
10
11
12
13
14
15	EXPECTED RESULT
16	Name	Dept	PAPER	BOOK
17	Name2	B	2	4
18
19
20
21
22
23
24
Sheet14

when i search for name and dept. I would like it to filter the column header ( pen,paper,pencil and book) with the respective figures below. and will ignore zero values and empty cells. see expected result.

 

[Peter_SSs]

Try this.

23 08 20.xlsm
	A	B	C	D	E	F
1
2	Name	Dept	PEN	PAPER	PENCIL	BOOK
3	Name1	A	2			8
4	Name2	B	0	2		4
5	Name3	C	2			1
6	Name4	D	2	0	3	1
7
14
15	EXPECTED RESULT
16	Name	Dept	PAPER	BOOK
17	Name2	B	2	4
18
Filter

Cell Formulas
Range	Formula
C16:D17	C16	=LET(f,FILTER(C2:F6,ISNUMBER(MATCH(A2:A6,A16:A17,0))),FILTER(f,TAKE(f,-1)<>0))
Dynamic array formulas.

 

[ExcelNewbie2020]

Try this.

23 08 20.xlsm
	A	B	C	D	E	F
1
2	Name	Dept	PEN	PAPER	PENCIL	BOOK
3	Name1	A	2			8
4	Name2	B	0	2		4
5	Name3	C	2			1
6	Name4	D	2	0	3	1
7
14
15	EXPECTED RESULT
16	Name	Dept	PAPER	BOOK
17	Name2	B	2	4
18
Filter

Cell Formulas
Range	Formula
C16:D17	C16	=LET(f,FILTER(C2:F6,ISNUMBER(MATCH(A2:A6,A16:A17,0))),FILTER(f,TAKE(f,-1)<>0))
Dynamic array formulas.

thank you sir for the immediate reply, could you please add the dept as another criteria?.. thanks again

 

[Peter_SSs]

Is this what you mean?

23 08 20.xlsm
	A	B	C	D	E	F
1
2	Name	Dept	PEN	PAPER	PENCIL	BOOK
3	Name1	A	2			8
4	Name2	B	0	2		4
5	Name2	C	2			1
6	Name4	D	2	0	3	1
7
14
15	RESULT
16	Name	Dept	PEN	BOOK
17	Name2	C	2	1
18
Filter

Cell Formulas
Range	Formula
C16:D17	C16	=LET(f,FILTER(C2:F6,ISNUMBER(MATCH(A2:A6,A16:A17,0)*MATCH(B2:B6,B16:B17,0))),FILTER(f,TAKE(f,-1)<>0))
Dynamic array formulas.

 

[ExcelNewbie2020]

Is this what you mean?

23 08 20.xlsm
	A	B	C	D	E	F
1
2	Name	Dept	PEN	PAPER	PENCIL	BOOK
3	Name1	A	2			8
4	Name2	B	0	2		4
5	Name2	C	2			1
6	Name4	D	2	0	3	1
7
14
15	RESULT
16	Name	Dept	PEN	BOOK
17	Name2	C	2	1
18
Filter

Cell Formulas
Range	Formula
C16:D17	C16	=LET(f,FILTER(C2:F6,ISNUMBER(MATCH(A2:A6,A16:A17,0)*MATCH(B2:B6,B16:B17,0))),FILTER(f,TAKE(f,-1)<>0))
Dynamic array formulas.

Indeed.. thank you very much sir..

 

[Peter_SSs]

Cheers. You're welcome.