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

ExcelNewbie2020

Board Regular
Joined
Dec 3, 2020
Messages
139
Office Version
  1. 365
Platform
  1. Windows
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​
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
3,294
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
You mean filtering a table but without including in the result the filtering column?
In this case, try in G1:
Excel Formula:
=FILTER(A1:D11,(E1:E11=1)+(E1:E11="Number"))
 
Upvote 0

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
58,634
Office Version
  1. 365
Platform
  1. Windows
Like this?

22 11 05.xlsm
ABCDEFGHIJK
1NameEmp.#DateDept.Number
2Name1100111/05/2022A1Name1100111/05/2022A
3Name2100211/06/2022B2Name3100311/07/2022C
4Name3100311/07/2022C1Name5100511/09/2022B
5Name4100411/08/2022A2Name7100711/11/2022A
6Name5100511/09/2022B1Name9100913/11/2022C
7Name6100611/10/2022C2
8Name7100711/11/2022A1
9Name8100811/12/2022B2
10Name9100913/11/2022C1
11Name10101014/11/2022A2
FILTER
Cell Formulas
RangeFormula
H2:K6H2=FILTER(A2:D11,E2:E11=1)
Dynamic array formulas.
 
Upvote 0

ExcelNewbie2020

Board Regular
Joined
Dec 3, 2020
Messages
139
Office Version
  1. 365
Platform
  1. Windows
Like this?

22 11 05.xlsm
ABCDEFGHIJK
1NameEmp.#DateDept.Number
2Name1100111/05/2022A1Name1100111/05/2022A
3Name2100211/06/2022B2Name3100311/07/2022C
4Name3100311/07/2022C1Name5100511/09/2022B
5Name4100411/08/2022A2Name7100711/11/2022A
6Name5100511/09/2022B1Name9100913/11/2022C
7Name6100611/10/2022C2
8Name7100711/11/2022A1
9Name8100811/12/2022B2
10Name9100913/11/2022C1
11Name10101014/11/2022A2
FILTER
Cell Formulas
RangeFormula
H2:K6H2=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
 
Upvote 0

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
58,634
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJK
1NameEmp.#DateDept.Number
2Name1100111/05/2022A1Name1100111/05/2022A
3Name2100211/06/2022B2Name3100311/07/2022C
4Name3100311/07/2022C1Name5100511/09/2022B
5Name4100411/08/2022A2Name7100711/11/2022A
6Name5100511/09/2022B1Name9100913/11/2022C
7Name6100611/10/2022C2
8Name7100711/11/2022A1
9Name8100811/12/2022B2
10Name9100913/11/2022C1
11Name10101014/11/2022A2
12
FILTER (2)
Cell Formulas
RangeFormula
H2:K6H2=FILTER(Table1[[Name]:[Dept.]],Table1[Number]=1)
Dynamic array formulas.
 
Upvote 0

ExcelNewbie2020

Board Regular
Joined
Dec 3, 2020
Messages
139
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJK
1NameEmp.#DateDept.Number
2Name1100111/05/2022A1Name1100111/05/2022A
3Name2100211/06/2022B2Name3100311/07/2022C
4Name3100311/07/2022C1Name5100511/09/2022B
5Name4100411/08/2022A2Name7100711/11/2022A
6Name5100511/09/2022B1Name9100913/11/2022C
7Name6100611/10/2022C2
8Name7100711/11/2022A1
9Name8100811/12/2022B2
10Name9100913/11/2022C1
11Name10101014/11/2022A2
12
FILTER (2)
Cell Formulas
RangeFormula
H2:K6H2=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
 
Upvote 0

HUNNY

New Member
Joined
Nov 3, 2022
Messages
14
Office Version
  1. 365
Platform
  1. Windows
I did not understand the arguments provided in the formula.
What arguments you provided in index formula.
 
Upvote 0

HUNNY

New Member
Joined
Nov 3, 2022
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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)).
 
Last edited:
Upvote 0

Forum statistics

Threads
1,187,202
Messages
5,962,183
Members
438,592
Latest member
vincethesun

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top