filter formula if the criteria is not included in the filtered info

[ExcelNewbie2020]

How can I use the filtered formula if the criteria is not included on the info to be filtered. Please see below;
I would like to filter the name,emp.#,date and dept., if "number" column is 1. the "number" column is not included on the info to be filtered. Hope I explain it well.. many thanks

Name​	Emp.#​	Date​	Dept.​	Number​			RESULT IF FILTER CRITERIA IS "1"​
Name1​	1001​	05-11-22​	A​	1​			Name​	Emp.#​	Date​	Dept.​
Name2​	1002​	06-11-22​	B​	2​			Name1​	1001​	05-11-22​	A​
Name3​	1003​	07-11-22​	C​	1​			Name3​	1003​	07-11-22​	C​
Name4​	1004​	08-11-22​	A​	2​			Name5​	1005​	09-11-22​	B​
Name5​	1005​	09-11-22​	B​	1​			Name7​	1007​	11-11-22​	A​
Name6​	1006​	10-11-22​	C​	2​			Name9​	1009​	13-11-22​	C​
Name7​	1007​	11-11-22​	A​	1​
Name8​	1008​	12-11-22​	B​	2​
Name9​	1009​	13-11-22​	C​	1​
Name10​	1010​	14-11-22​	A​	2​

 

[Peter_SSs]

How did you get both minimum and maximum values from using small function in AGGREGATE formula.

Are you posting in the correct thread?

what will be the formula if i want to filter only name, emp# and dept. (skip on date)? thanks

Is this what you mean? Check table name.

=FILTER(Table13[[Name]:[Date]],Table13[Number]=1)

 

[ExcelNewbie2020]

Are you posting in the correct thread?



Is this what you mean? Check table name.

=FILTER(Table13[[Name]:[Date]],Table13[Number]=1)

Sir, I mean, after filter, it would be name, emp.# and dept only.. the date which is in between is not included.. thanks

 

[Peter_SSs]

Do you have the CHOOSECOLS function?

ExcelNewbie2020_1.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Name	Emp.#	Date	Dept.	Number			Name	Emp.#	Dept.
2	Name1	1001	11/05/2022	A	1			Name1	1001	A
3	Name2	1002	11/06/2022	B	2			Name3	1003	C
4	Name3	1003	11/07/2022	C	1			Name5	1005	B
5	Name4	1004	11/08/2022	A	2			Name7	1007	A
6	Name5	1005	11/09/2022	B	1			Name9	1009	C
7	Name6	1006	11/10/2022	C	2
8	Name7	1007	11/11/2022	A	1
9	Name8	1008	11/12/2022	B	2
10	Name9	1009	13/11/2022	C	1
11	Name10	1010	14/11/2022	A	2
FILTER (4)

Cell Formulas
Range	Formula
H2:J6	H2	=CHOOSECOLS(FILTER(Table134[[Name]:[Dept.]],Table134[Number]=1),1,2,4)
Dynamic array formulas.

 

[ExcelNewbie2020]

Do you have the CHOOSECOLS function?

ExcelNewbie2020_1.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Name	Emp.#	Date	Dept.	Number			Name	Emp.#	Dept.
2	Name1	1001	11/05/2022	A	1			Name1	1001	A
3	Name2	1002	11/06/2022	B	2			Name3	1003	C
4	Name3	1003	11/07/2022	C	1			Name5	1005	B
5	Name4	1004	11/08/2022	A	2			Name7	1007	A
6	Name5	1005	11/09/2022	B	1			Name9	1009	C
7	Name6	1006	11/10/2022	C	2
8	Name7	1007	11/11/2022	A	1
9	Name8	1008	11/12/2022	B	2
10	Name9	1009	13/11/2022	C	1
11	Name10	1010	14/11/2022	A	2
FILTER (4)

Cell Formulas
Range	Formula
H2:J6	H2	=CHOOSECOLS(FILTER(Table134[[Name]:[Dept.]],Table134[Number]=1),1,2,4)
Dynamic array formulas.

yes i have choosecols and that works, thank you man...

 

[Jborg]

Book1
	B	C	D	E	F	G	H	I	J	K
1
2	Name	Emp.#	Date	Dept.	Number
3	Name1	1001	44692	A	1			Name1	1001	A
4	Name2	1002	44723	B	2			Name3	1003	C
5	Name3	1003	44753	C	1			Name5	1005	B
6	Name4	1004	44784	A	2			Name7	1007	A
7	Name5	1005	44815	B	1			Name9	1009	C
8	Name6	1006	44845	C	2
9	Name7	1007	11/11/2022	A	1
10	Name8	1008	44906	B	2
11	Name9	1009	44878	C	1
12	Name10	1010	44879	A	2
13
Sheet2

Cell Formulas
Range	Formula
I3:K7	I3	=FILTER(FILTER(Table1[[Name]:[Dept.]],Table1[Number]=1),{1,1,0,1})
Dynamic array formulas.