Advanced filter 'Not Like'

[tweedle]

I'm trying to run a manual A/F (no vba)

I have this criteria in the same cell [have gone through multiple iterations].
*DOW* - works, but includes 'windows' which is not desired.
LIKE "*dow*" and NOT LIKE "*windows*" -failed
*DOW* AND "<>*WIN*" - failed

Essentially, I'm trying to grab the records that match the subset LIKE *DOW* and exclude LIKE *windows* -- (I'm reading that now and not even sure it logically makes sense)

The Q: How to syntax the same-cell filter?

Appreciate any guidance.

 

[GlennUK]

Have the criteria in different cells in the same row, both with headers for the field.

 

[BobUmlas]

If the column with the text is A, then you can use this formula for the advanced filter criteria in say, K2 (k1 is blank):
=AND(NOT(ISERROR(FIND("dow",A2))),IF(ISNUMBER(FIND("windows",A2)),FALSE,TRUE))

 

[tweedle]

Bob, Glenn; Thanks for the guidance!
Essentially it's not same-cell at all ... Helper Columns!
Thanks!

 

[Weaver]

I checked out the 'multiple header' idea of glenns, it seems that

Criteria in same column = OR
Criteria in same row = AND

Of course, for the AND condtion, you do need to repeat the headers

I had no idea you could do this.

And as for using a formula for the criteria, I remember coming across this a while ago - looks very powerful but by no means easy.

 

[GlennUK]

Bob, Glenn; Thanks for the guidance!
Essentially it's not same-cell at all ... Helper Columns!
Thanks!

Mine is 2 cells of criteria ( plus 2 header cells ).

Bob's is one cell, as you requested ( with a blank header cell ).

No need for helper columns.

 

[mikerickson]

No need for helper columns in the data range
A two column Criteria range with '=*DOW* and '<>*WIN* worked for me.

 

[tweedle]

Thanks for all the input; issue resolved.
#1 Learned that the Headerless formula essentially replicates itself across the data range, creating logicals and filters out the FALSE - very cool - got that method to work.
#2 Learned that the multi-column, with headers, criteria should have been prefaced (sp) with the apostrophe. - got that to work as well.

Thanks again!
A pool of genuis this forum is.