Excluding 5 sets of data in filter formula

Brentsa

Board Regular
Joined
Oct 3, 2013
Messages
118
Office Version
  1. 365
Platform
  1. Windows
I want to filter a data table and want to exclude 5 sets of data found in this table I've tried this formula to start with the exclusion of the first data but I'm not getting the correct answer. But maybe I'm doing something wrong.
=FILTER('Teller Stats'!B7:L1000,ISNA(MATCH('Teller Stats'!L7:L1000=0,)))

the table is: Teller Stats'!B7:L1000

I want the data with any amount bigger or smaller than 0 to be excluded in Column L
I want all blank cells in Column E to be excluded
I want any cells that has any of the following 3 strings to be excluded from column F:
Deposita; CashNet; Cash Connect

So the final result will give me everything else found in: Teller Stats'!B7:L1000
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Could you give us a small set (say max 15-20 rows) of dummy data and expected results with XL2BB and explain again in relation to that sample?
 
Upvote 0
ABCDEFGHIJKL
1Headings
2Max
123456789​
12 Dec​
123456789​
123​
Cashnet
123456789​
123456789​
10,000.00​
10,000.00​
0.00​
0.00​
3Phil
123456789​
12 Dec​
123456789​
123Mary Cash Connect
123456789​
123456789​
100,000.00​
100,000.00​
0.00​
0.00​
4Max
123456789​
12 Dec​
123456789​
123​
Pies
123456789​
123456789​
100,002.00​
100,000.00​
0.00​
2.00​
5Jane
123456789​
12 Dec​
123456789​
Chips
123456789​
123456789​
987,000.00​
987,000.00​
0.00​
0.00​
6Jane
123456789​
12 Dec​
123456789​
123​
Coke
123456789​
123456789​
123,548.00​
123,548.00​
0.00​
0.00​
7Jane
123456789​
12 Dec​
123456789​
123​
July
123456789​
123456789​
123,654.00​
123,654.00​
0.00​
0.00​
8Max
123456789​
12 Dec​
123456789​
123​
Ipecs
123456789​
123456789​
20,000.00​
20,00.00​
0.00​
0.00​
9Phil
123456789​
12 Dec​
123456789​
IpecsDeposita
123456789​
123456789​
25,000.00​
0.00​
0.00​
25,000.00​
10Max
123456789​
12 Dec​
123456789​
123​
John
123456789​
123456789​
64,000.00​
64,000.00​
0.00​
0.00​

As per the above I want my filter to Exclude Rows:
2 - F has Cashnet
3 - F has Cash Connect
4 - L has a amount
5 - E is blank
9- E is blank; F has Deposita; L has a number

so my filter should give me the results for rows
6
7
8
10
 
Upvote 0
Thanks for the data and further explanation. Try this

Brentsa.xlsm
ABCDEFGHIJKL
1Headings
2Max12345678912-Dec123456789123Cashnet12345678912345678910,000.0010,000.0000
3Phil12345678912-Dec123456789123Mary Cash Connect123456789123456789100,000.00100,000.0000
4Max12345678912-Dec123456789123Pies123456789123456789100,002.00100,000.0002
5Jane12345678912-Dec123456789Chips123456789123456789987,000.00987,000.0000
6Jane12345678912-Dec123456789123Coke123456789123456789123,548.00123,548.0000
7Jane12345678912-Dec123456789123July123456789123456789123,654.00123,654.0000
8Max12345678912-Dec123456789123Ipecs12345678912345678920,000.0020,00.0000
9Phil12345678912-Dec123456789IpecsDeposita12345678912345678925,000.000025,000.00
10Max12345678912-Dec123456789123John
11
12
13
14Jane12345678944177123456789123Coke12345678912345678912354812354800
15Jane12345678944177123456789123July12345678912345678912365412365400
16Max12345678944177123456789123Ipecs1234567891234567892000020,00.0000
17Max12345678944177123456789123John000000
Sheet2
Cell Formulas
RangeFormula
A14:L17A14=FILTER(A2:L10,(ISERROR(SEARCH("Cashnet",F2:F10))+ISERROR(SEARCH("Cash Connect",F2:F10))+ISERROR(SEARCH("Deposita",F2:F10))=3)*(L2:L10=0)*(E2:E10<>""),"")
Dynamic array formulas.
 
Upvote 0
Solution

Forum statistics

Threads
1,212,931
Messages
6,110,745
Members
448,295
Latest member
Uzair Tahir Khan

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
Back
Top