Custom Filter

[Rob]

Hi,
I'd like to have more than the two criteria to filter some data, to filter
out all those rows that contain Mr, Mrs, Miss, Dr, The, etc. etc. Is there a
way to do something like this?
I can see that I may need as many as 8 or more.

Selection.AutoFilter Field:=1, Criteria1:="<>*Mr*", _
Operator:=xlAnd, Criteria2:="<>*Mrs*", _
Operator:=xlAnd, Criteria3:="<>*Miss*", _
Operator:=xlAnd, Criteria4:="<>*Dr*"

Rob

 

[CaptainQuattro]

My approach would be to create an extra column in the worksheet with
formula that tests all of your criteria and returns a True or Fals
Result.

Then instead of Filtering on the Mr. Mrs. Dr. column, filter on th
True/False
Column

 

[Rob]

Thanks for that. I do realise I can do that, but I was hoping to avoid
having to include any more formulas than those already in the workbook. Is
there no way to do this in VBA?

Rob

"CaptainQuattro"

 

[Jim May]

Check out the Advanced Filter; It's better equipped to
do all that you want to do..

 

[Dave Peterson]

You may want to look at Data|Filter|Advanced filter and use a criteria range.

Debra Dalgleish has some notes:
http://www.contextures.com/xladvfilter02.html

 

[Rob]

Thanks Jim. I had a quick fiddle with that and it may work if I can put in
criteria that will do what I want. The problem with my situation is that
I'm trying to filter OUT any rows in the column that may contain Mr, Mrs,
etc., etc. leaving behind the data I do want to see ie I only want rows to
show where those prefixes don't exist in the cells.

eg. If my data in Column B was as follows:

Mr & Mrs Brown
Mrs White
Brown Community Centre
Dr Black
Black Medical Centre
Miss Pink

I want to filter out all but Brown Community Centre and Black Medical Centre
excluding any Mr, Mrs, Dr and Miss, etc.

Any ideas how I would do that?

(I feel VBA should still be able to do this somehow and I would prefer to do
it that way. But, if VBA cannot, then I'll continue to proceed with the
advanced filter method.)


Rob

 

[Peo Sjoblom]

Title called Names in A3, your posted sample starting in A4 going down to A9

Criteria in D12, leave D1 blank and in D2 put

=OR(ISNUMBER(SEARCH({"Mr ";"Mrs ";"Dr ";"Miss "},A4)))=FALSE

select the table from A3:A9, do data>filter>advanced filter use

$D$1:$D$2

for criteria range



--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[Rob]

Thankyou so much, Peo. Works great!

Could you please explain what the {} brackets do, as I'm not familiar with
those in formula construction.
And also the ; use if you can.

Rob

 

[Rob]

Thanks Dave,

I was not aware of the advanced filter option....the wonders of Microsoft
hiding those options you don't regularly use when you use the pull down
menus!!

Rob

 

[Rob]

Hi Peo,

Just one more question please.
In the formula: =OR(ISNUMBER(SEARCH({"Mr ";"Mrs ";"Dr ";"Miss
"},A4)))=FALSE,
can the Mr, Mrs, etc. be obtained from Cell references so I can type those
criteria in specific cells and the formula picks those up from there?

Rob

 

[Peo Sjoblom]

Yes, you can actually hard code it and link to cells where you type in the
criteria you don't want to include

remove the formula from D2, in D1:G1 put

Names Names Names Names

in D2 put ="<>*"&I1&" *"
in E2 ="<>*"&J1&" *"
in F2 ="<>*"&K1&" *"
in G2 ="<>*"&L1&" *"

now in I1 put for instance Mr, in J1 Mrs, in K1 Dr and in L1 Miss

apply the filter and as criteria range use

$D$1:$G$2



will return


Names
Brown Community Centre
Black Medical Centre

that way you can type in the criteria in I1:L1, change the criteria to for
instance D1:E2 and use only 2 criteria

as you see I use a space before the last wildcards, if I didn't Dr would
filter Drive as well

the advanced filter is a very powerful once one learns the somewhat strange
way of the conditions, the criteria and setup is very similar to the D
function

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

 

[Jim May]

Peo,
I see why the use of the "OR" operator in D2, as it
allows for a full matrix to be created against the
{ } Items.

But Why the Blank (empty) Cell D1?
Tks,

Jim May

 

[John James]

Hi Peo,

I'm comfortable with your later advanced filter formulae, but am
fascinated by your computed filter criteria formula:
=OR(ISNUMBER(SEARCH({"Mr ";"Mrs ";"Dr ";"Miss "},A4)))=FALSE

I'd also love to hear your explanation of the logic. A great
opportunity to learn. I understand you originally entered this as a
new computed criteria, and I understood that D1 couldn't be "Names" as
it was a new computed field in the database (albeit calculated based on
this field), and that title ("Names") had been taken already, but I also
didn't realise it's title could be left blank. I assumed a title of
some sort was necessary. Is your formula entered as an array? My
understanding is that Isnumber(Search("Mr "),A4) would evaluate to
False where "Mr " wasn't found and to True where it was. Like Rob and
Jim I'd like to better understand the logic of you extending the array
to incorporate multiple tests using the braces, semicolons and the Or
statement.

Thanks,

 

[Jim May]

The six records are (internally) 4 arrayed based on the input
of the guys within the { }'s .

Col 1 is Mr
Col 2 is Mrs
Col 3 is Dr
Col 4 is Miss

*** Matrix Created Below ***
T T F F
F T F F
F F F F
F F T F
F F F F
F F F T


Any row(s) that contain 100% F, means If pure Falses, then = False is
TRUE so Rows 3 and 5 = TRUE
All other rows Col 1-4 contain at least one (1) True, So if any one = True,
then = False is False

Confusing, right....
Oh well, that's the way it works.
The only thing I don't understand is why cell D1 is left BLANK...???

Hope this helps, (HTH)
Jim May

 

[John James]

Thanks, Jim

It's been a bit foreign not having a programming background, but I
think I've got it now through your help. So in the formula
=OR(ISNUMBER(SEARCH({"Mr ";"Mrs ";"Dr ";"Miss "},A4)))=FALSE:

1. The Braces identify the individual entries in an array. Call it
Array1.
2. The semicolons indicate that Array1 is horizontal (across columns)
as compared to commas which indicate that an array is vertical (across
rows)
3. The A4 reference locates the cell tested (the first entry in the
Array2 to be evaluated)
4. The search formula evaluates each item in the 6 (Array2) x 4
(Array1) array to a number, if found, otherwise to an error. This
produces Array3.
5. The Isnumber converts Array3 (these 6x4 numbers/errors) to Array4
(another 6x4 True/False table of matches between the two arrays)
6. The Or evaluates each row of the Array4 to produce Array5 (6x1).
These rows evaluate to True if any true exists within the Row.
7. The =False convert these six row Boolean values to the opposite
Boolean. This is no longer a 6x1 array but an individual formula,
which is why the formula doesn't need to be entered as an array. I
assume you could just as easily use the Not() formula instead of
=False.

Re your query, Jim, the fact that Peo's formula works appears to prove
that a heading hasn't been forced by the Excel Developers for computed
criterion. Whilst I was surprised, I can't see any problem in them
taking this approach. This criterion entry was not like a standard
filter criterion where you need to identify the table's row heading so
that Excel knows which column to apply the criterion to. In this
instance, Peo's method is a computed filter criteria. Rather than
adding a new column to the table (which would require a new heading),
this "column" of data is held in memory without specific cell locations
to attach to. Excel knows which column to base the calculations on
through the specification of cell A4 in Peo's formula.

 

[Peo Sjoblom]

Jim,

D1 is left blank because D2 is a formula using the first data cell so it
doesn't need the heading and if it would be included it would try to look
for the literal expression of the formula in the list and return just the
header when filtered
The reference to the first data cell in the list will tell Excel in what
range to look

I posted another method using 4 headers and 4 extra cells for inputting the
criteria so using a formula can be space saving but since the OP wanted to
put the criteria in cells I posted another solution as well
(you can still use a formula that would reference the cells but it would be
much bigger)

Peo

 

[Peo Sjoblom]

Hi John,

Thanks, Jim

It's been a bit foreign not having a programming background, but I
think I've got it now through your help. So in the formula
=OR(ISNUMBER(SEARCH({"Mr ";"Mrs ";"Dr ";"Miss "},A4)))=FALSE:

1. The Braces identify the individual entries in an array. Call it
Array1.
2. The semicolons indicate that Array1 is horizontal (across columns)
as compared to commas which indicate that an array is vertical (across
rows)
3. The A4 reference locates the cell tested (the first entry in the
Array2 to be evaluated)
4. The search formula evaluates each item in the 6 (Array2) x 4
(Array1) array to a number, if found, otherwise to an error. This
produces Array3.
5. The Isnumber converts Array3 (these 6x4 numbers/errors) to Array4
(another 6x4 True/False table of matches between the two arrays)
6. The Or evaluates each row of the Array4 to produce Array5 (6x1).
These rows evaluate to True if any true exists within the Row.
7. The =False convert these six row Boolean values to the opposite
Boolean. This is no longer a 6x1 array but an individual formula,
which is why the formula doesn't need to be entered as an array. I
assume you could just as easily use the Not() formula instead of
=False.

Definitely, that would be the orthodox way to do it, it's just me, I don't
like the word NOT <bg>
so somethimes I play around a bit. But you are right, in Excel school I
would probably have used

=NOT(OR(ISNUMBER(SEARCH({"Mr ";"Mrs ";"Dr ";"Miss "},A4))))

The thing with advanced filter formulas is that sometimes you have to
reverse the normal thinking with arrays where you try to find the logic to
create TRUE and convert them to 1s whereas in this case you want to exclude
some values and for these values you are looking for a way to return FALSE

Re your query, Jim, the fact that Peo's formula works appears to prove
that a heading hasn't been forced by the Excel Developers for computed
criterion. Whilst I was surprised, I can't see any problem in them
taking this approach. This criterion entry was not like a standard
filter criterion where you need to identify the table's row heading so
that Excel knows which column to apply the criterion to. In this
instance, Peo's method is a computed filter criteria. Rather than
adding a new column to the table (which would require a new heading),
this "column" of data is held in memory without specific cell locations
to attach to. Excel knows which column to base the calculations on
through the specification of cell A4 in Peo's formula.

Correct, if there are more columns you need to filter you can include cells
from those columns in a formula as well.

e.g.

Assume we have the same setup and wants to exclude the titles (Mrs Mr etc)
and we also have values in B,
assume we have a header called Values and

1
2
3
4
5
6

in B4:B9

so we want exclude the Mrs.Mr/Dr/Miss titles and exclude values less than 5

meaning if done correctly we should get

Names Values
Black Medical Centre 5

the formula could look like

=AND(NOT(OR(ISNUMBER(SEARCH({"Mr ","Mrs ","Dr ","Miss "},A4)))),B4>4)

(using NOT this time)


The advanced filter is very powerful to use once you get the hang of the
weird setup,
it's very fast and on the plus side is that if you learn it you will know
how to use the D functions as well


Peo

 

[Jim May]

Thanks Peo for the explanation.
Jim

 

[John James]

Thanks Peo,

Amazing how much I've learnt from a single formula. Probably take a
good few more tough examples before it begins to feel natural though.
The concept of Arrays still blows my mind a bit at times.

I actually felt your =False made more sense than Not() in the context,
even though the two expressions produced the same result.

Re Jim's query, my understanding is that D1 can also be non-blank,
where D2 is a formula, as long as that D1 heading doesn't match an
existing database heading for the filter.

Thanks again to you and Jim.