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

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

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


--
CaptainQuattro
------------------------------------------------------------------------
CaptainQuattro's Profile: http://www.excelforum.com/member.php...o&userid=32763
View this thread: http://www.excelforum.com/showthread...hreadid=527941

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"
wrote in
message news:CaptainQuattro.25gwsm_1143697200.512@excelfor um-nospam.com...

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

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


--
CaptainQuattro
------------------------------------------------------------------------
CaptainQuattro's Profile:
http://www.excelforum.com/member.php...o&userid=32763
View this thread: http://www.excelforum.com/showthread...hreadid=527941

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 wrote:

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

--

Dave Peterson

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

"Dave Peterson" wrote in message
...
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 wrote:

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

--

Dave Peterson

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

"Rob" wrote in message
...
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

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

"Jim May" wrote in message
news:U9ZWf.39942$KE1.13231@dukeread02...
Check out the Advanced Filter; It's better equipped to
do all that you want to do..

"Rob" wrote in message
...
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

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

Criteria in D1:D2, leave D1 blank and in D2 put

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

select the table from A3:A9, do datafilteradvanced filter use

$D$1:$D$2

for criteria range



--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"Rob" wrote in message
...
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

"Jim May" wrote in message
news:U9ZWf.39942$KE1.13231@dukeread02...
Check out the Advanced Filter; It's better equipped to
do all that you want to do..

"Rob" wrote in message
...
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

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

"Peo Sjoblom" wrote in message
...
Title called Names in A3, your posted sample starting in A4 going down to
A9

Criteria in D1:D2, leave D1 blank and in D2 put

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

select the table from A3:A9, do datafilteradvanced filter use

$D$1:$D$2

for criteria range



--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"Rob" wrote in message
...
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

"Jim May" wrote in message
news:U9ZWf.39942$KE1.13231@dukeread02...
Check out the Advanced Filter; It's better equipped to
do all that you want to do..

"Rob" wrote in message
...
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

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

"Rob" wrote in message
...
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

"Peo Sjoblom" wrote in message
...
Title called Names in A3, your posted sample starting in A4 going down to
A9

Criteria in D1:D2, leave D1 blank and in D2 put

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

select the table from A3:A9, do datafilteradvanced filter use

$D$1:$D$2

for criteria range



--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"Rob" wrote in message
...
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

"Jim May" wrote in message
news:U9ZWf.39942$KE1.13231@dukeread02...
Check out the Advanced Filter; It's better equipped to
do all that you want to do..

"Rob" wrote in message
...
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

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

"Peo Sjoblom" wrote in message
...
Title called Names in A3, your posted sample starting in A4 going down to
A9

Criteria in D1:D2, leave D1 blank and in D2 put

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

select the table from A3:A9, do datafilteradvanced filter use

$D$1:$D$2

for criteria range



--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"Rob" wrote in message
...
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

"Jim May" wrote in message
news:U9ZWf.39942$KE1.13231@dukeread02...
Check out the Advanced Filter; It's better equipped to
do all that you want to do..

"Rob" wrote in message
...
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

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,


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=527941