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​

 

[Anthony47]

You mean filtering a table but without including in the result the filtering column?
In this case, try in G1:

=FILTER(A1:D11,(E1:E11=1)+(E1:E11="Number"))

 

[Peter_SSs]

Like this?

22 11 05.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Name	Emp.#	Date	Dept.	Number
2	Name1	1001	11/05/2022	A	1			Name1	1001	11/05/2022	A
3	Name2	1002	11/06/2022	B	2			Name3	1003	11/07/2022	C
4	Name3	1003	11/07/2022	C	1			Name5	1005	11/09/2022	B
5	Name4	1004	11/08/2022	A	2			Name7	1007	11/11/2022	A
6	Name5	1005	11/09/2022	B	1			Name9	1009	13/11/2022	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

Cell Formulas
Range	Formula
H2:K6	H2	=FILTER(A2:D11,E2:E11=1)
Dynamic array formulas.

 

[ExcelNewbie2020]

Like this?

22 11 05.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Name	Emp.#	Date	Dept.	Number
2	Name1	1001	11/05/2022	A	1			Name1	1001	11/05/2022	A
3	Name2	1002	11/06/2022	B	2			Name3	1003	11/07/2022	C
4	Name3	1003	11/07/2022	C	1			Name5	1005	11/09/2022	B
5	Name4	1004	11/08/2022	A	2			Name7	1007	11/11/2022	A
6	Name5	1005	11/09/2022	B	1			Name9	1009	13/11/2022	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

Cell Formulas
Range	Formula
H2:K6	H2	=FILTER(A2:D11,E2:E11=1)
Dynamic array formulas.

ouchhh.. this is the same formula i am using in my original file, but it doesn't work, and i didn't tried it on my dummy file.. However, when i convert my table to range and use this formula it actually worx.. thanks man

 

[Peter_SSs]

However, when i convert my table to range ...

If the original data is a formal table (not indicated in your original post), then you should be able to use something like this

ExcelNewbie2020_1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Name	Emp.#	Date	Dept.	Number
2	Name1	1001	11/05/2022	A	1			Name1	1001	11/05/2022	A
3	Name2	1002	11/06/2022	B	2			Name3	1003	11/07/2022	C
4	Name3	1003	11/07/2022	C	1			Name5	1005	11/09/2022	B
5	Name4	1004	11/08/2022	A	2			Name7	1007	11/11/2022	A
6	Name5	1005	11/09/2022	B	1			Name9	1009	13/11/2022	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
12
FILTER (2)

Cell Formulas
Range	Formula
H2:K6	H2	=FILTER(Table1[[Name]:[Dept.]],Table1[Number]=1)
Dynamic array formulas.

 

[ExcelNewbie2020]

If the original data is a formal table (not indicated in your original post), then you should be able to use something like this

ExcelNewbie2020_1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Name	Emp.#	Date	Dept.	Number
2	Name1	1001	11/05/2022	A	1			Name1	1001	11/05/2022	A
3	Name2	1002	11/06/2022	B	2			Name3	1003	11/07/2022	C
4	Name3	1003	11/07/2022	C	1			Name5	1005	11/09/2022	B
5	Name4	1004	11/08/2022	A	2			Name7	1007	11/11/2022	A
6	Name5	1005	11/09/2022	B	1			Name9	1009	13/11/2022	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
12
FILTER (2)

Cell Formulas
Range	Formula
H2:K6	H2	=FILTER(Table1[[Name]:[Dept.]],Table1[Number]=1)
Dynamic array formulas.

i mean, the sheet where i want to show the formula result is on table format

 

[Peter_SSs]

i mean, the sheet where i want to show the formula result is on table format

I'm not sure what you are saying.

 

[HUNNY]

I did not understand the arguments provided in the formula.
What arguments you provided in index formula.

 

[ExcelNewbie2020]

I'm not sure what you are saying.

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

 

[HUNNY]

Like after assigning array to the index number, it asked for row(That part i am unable to understand).
How did you get both minimum and maximum values from using small function in AGGREGATE formula.
Minimum value in "from depth" column and maximum value in "to depth " column.
Why after using ROW(s), you used /(s=I2)*(offset s 1 0)<>I2)).