advanced filtering multiple criteria

[Spencer Hutton]

I am trying to use advance filtering to filter down a list given 2 criteria.
it will be more than 2 in some cases, but for the simplicity of this post,
i'll say 2. the Description column... i want to filter it down to rrecords
that contain the text "fireplace" and do NOT contain the text "outdoor".
(tryinging to isolate indoor fireplaces only. the criteria i came up with is:
="<>*outdoor*"
="=*fireplace*"
but when i filter, it doesn't seem, to be filtering down the list for all
the criteria... only the first one. Yes, i am selecting the criteria range
with the header and both criteria. i have had this work with both criteria
individually, but not together. TIA.

 

[Dave Peterson]

I'd start by reading Debra Dalgleish's site, specifically this page:
http://contextures.com/xladvfilter01.html

I put my header in A1 ("description") and my data in A2:A15

It looked like:
description
sdfoutdoorqfireplacereqweroutdoorqwer
sdfoutdoorqfireplacereqweroutdoorqwer
sdfoutdoorqfireplacereqweroutdoorqwer
sdfoutdoorqfireplacereqweroutdoorqwer
asdffireplaceqwer
asdffireplaceqwer
asdffireplaceqwer
sdfoutdoorqfireplacereqweroutdoorqwer
sdfoutdoorqfireplacereqweroutdoorqwer
sdfoutdoorqfireplacereqweroutdoorqwer
sdfoutdoorqfireplacereqweroutdoorqwer
sdfoutdoorqfireplacereqweroutdoorqwer
sdfoutdoorqfireplacereqweroutdoorqwer
sdfoutdoorqfireplacereqweroutdoorqwer


And then I put my criteria headers in E1:F1 (two cells!) and the criteria in
E2:F2

description description
*fireplace* <>*outdoor*


And only these 3 rows remained visible:

asdffireplaceqwer
asdffireplaceqwer
asdffireplaceqwer


=======================
I'm not sure if this alternative works for you, but I'd use
data|filter|autofilter and then use a custom filter:
Contains: fireplace
and
does not contain: outdoor

Yeah, you can only have two criteria, so that may be trouble.

When I have lots of criteria, I'll sometimes add a helper column that contains a
formula that returns a true or false:

=and(countif(a2,"*fireplace*")>0),countif(a2,"*outdoor*")=0)

Then I'll drag that formula done the column and auto-filter by that column.

(I can use multiple helper columns if the rules get really complex <bg>.)

 

[Spencer Hutton]

wonderful response, thank you.

I'd start by reading Debra Dalgleish's site, specifically this page:
http://contextures.com/xladvfilter01.html

I put my header in A1 ("description") and my data in A2:A15

It looked like:
description
sdfoutdoorqfireplacereqweroutdoorqwer
sdfoutdoorqfireplacereqweroutdoorqwer
sdfoutdoorqfireplacereqweroutdoorqwer
sdfoutdoorqfireplacereqweroutdoorqwer
asdffireplaceqwer
asdffireplaceqwer
asdffireplaceqwer
sdfoutdoorqfireplacereqweroutdoorqwer
sdfoutdoorqfireplacereqweroutdoorqwer
sdfoutdoorqfireplacereqweroutdoorqwer
sdfoutdoorqfireplacereqweroutdoorqwer
sdfoutdoorqfireplacereqweroutdoorqwer
sdfoutdoorqfireplacereqweroutdoorqwer
sdfoutdoorqfireplacereqweroutdoorqwer


And then I put my criteria headers in E1:F1 (two cells!) and the criteria in
E2:F2

description description
*fireplace* <>*outdoor*


And only these 3 rows remained visible:

asdffireplaceqwer
asdffireplaceqwer
asdffireplaceqwer


=======================
I'm not sure if this alternative works for you, but I'd use
data|filter|autofilter and then use a custom filter:
Contains: fireplace
and
does not contain: outdoor

Yeah, you can only have two criteria, so that may be trouble.

When I have lots of criteria, I'll sometimes add a helper column that contains a
formula that returns a true or false:

=and(countif(a2,"*fireplace*")>0),countif(a2,"*outdoor*")=0)

Then I'll drag that formula done the column and auto-filter by that column.

(I can use multiple helper columns if the rules get really complex <bg>.)