PDA

View Full Version : Solved: More than 2 criteria - possible??



joelle
05-07-2007, 03:41 PM
Hello Experts,
I dont know if it is all possible to set custom autofilter that does more than 2 criteria. I have one piece of code below which part of a much longer module and I try to filter col A with 3 criteria like:
If cell value is "greater" than zero -- criteria 1
or
cell value is "equal" to ">" -- criteria 2 (looking at caret sign)
of
cell value is "equal" to "&" -- criteria 3 (looking at amper sign)

vba is:
Range("A9").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=">0", Operator:=xlOr, Criteria2:="=>"

Is this possible *without* using lookup. Cant afford lookup table in my actual very long module.
Many thanks.
J.

geekgirlau
05-07-2007, 08:43 PM
AFAIK you can't have more than the 2 criteria, but a way around this is to create a reference field in your data that evaluates the 3 options, and gives you a single value that you can then filter on.

For example, your formula might be =OR(A2>0,A2=">",A2="&"). This will create a value of either TRUE or FALSE, which you can then filter.

Bob Phillips
05-07-2007, 09:04 PM
Just add a filter to all the columns then run a 2 criteria filter, then a further filter.

joelle
05-08-2007, 09:06 AM
Hello,
Thank you both for the tip. I read but have never tried this kinda "advanced filter" and I try to avoid using a 2nd column for my vba, but I will explore this further. If it possible, pls would you attach a simple spreadsheet of how the true-false option works for more than 2 condition filtering.
Many thanks.
J.

lucas
05-08-2007, 09:55 AM
Attached is an example of a workaround to filtering by mult criteria...it copies to another worksheet.

joelle
05-08-2007, 10:15 AM
Hello Steve,
Thank you very much for the sample spreadsheet and the codes.
So I learn some advanced excel today. Thats very nice of you.
J.

lucas
05-08-2007, 10:21 AM
Hi J,
Not so advanced but very useful....post back if you run into problems....even after marking your thread solved.

joelle
05-08-2007, 11:32 AM
post back if you run into problems....even after marking your thread solved.

Steve - you read my mind .. I like what you said above.
Anyhow, I do not run into problems but I'm not savvy enough to apply the tips to my simple case. I definitely save your tips for my future endeavour.
Presently, I try to understand and be able to do excel feature "advanced filter" to filter "one column, 3 conditions". I followed the excel "help" menu but obviously its not helpful, so I attach here a very small spreadsheet with the hope I could get some simple step to get to the filtered list.
Many thanks.
Joelle.

lucas
05-08-2007, 11:55 AM
Hi joelle,
I don't think you can autofilter by more than 2 criteria without doing it twice as Bob suggests above but this will look for the blank cells and hide those rows...not sure if it's what your looking for.

joelle
05-08-2007, 01:24 PM
Steve,
I just try to do Advanced filter for "one column, multiple criteria" as shown under the Excel Help (F1), as attached, but I cant get there although I followed the steps.
I do not need to use vba to hide any row the cell is blank under "qty".
I need to do filtering, just like what is said under the help page that I attach here. If you do a help search for "advanced filter" from your Excel, you will see the same help instructions that I struggle with.
Thanks.
Joelle

lucas
05-08-2007, 02:04 PM
It doesn't seem to find the right arrow so I tried a wildcard charactor *

try it on the attachment....it seems to work

joelle
05-08-2007, 02:17 PM
It doesn't seem to find the right arrow so I tried a wildcard charactor *
try it on the attachment....it seems to work

Oh, thats why I struggled for too long just because of the ">" sign !!!
Your spreadsheet works beautifully.
Thank you Thank you Thank you Thank you !

Joelle

lucas
05-08-2007, 02:18 PM
Had me going to Joelle......glad we got it hashed.

joelle
05-08-2007, 04:43 PM
Hi Steve,

Sorry to become a bit greedy here .. wondering if I *may* ask for some frosting for my cake :blush

Since one of the 3 conditions there is "greater than zero", I put ">0" for the criteria range and it does the filter okay.
In reality however, I also have formula and other off-criteria texts in the qty column, and these get filtered as well.
I know there is some wrong with how I set "greater than zero" .. any ideal how to get rid of this flaw?
As said, this is frosting that I can live without. I feel odd now asking too much after my thread was hastily marked "solved" :(

Joelle

lucas
05-08-2007, 06:06 PM
Absolutly not a problem to ask but I'm not sure what your saying....is it that you have formula's with > in them and they are being caught in the filter?

joelle
05-09-2007, 08:28 AM
What I meant was:
criteria 1 - if cell value is > 0 - and I put in >0 for the criteria range
criteria 2 - if cell value is "*" (no problem here)
criteria 3 - if cell value is "&" (no problem here)

I think the function does not like >0 for criteria 1, and it filters everything else in column A, except for real blank cells. What I mean for "real blank" is, there are other blank cells there but they are not blank - they have formula in there and get filtered as well.
Maybe the criteria range (C1,C2,C3) only allows texts? :dunno

Joelle

lucas
05-09-2007, 11:06 AM
I tried several things and this seems to work Joelle...
I used *> for the criteria....
see attached.

joelle
05-09-2007, 11:24 AM
Hello Steve,

It does work very well!
I sincerely appreciate your lasting patience with my quest, your several postbacks, and your precious time.

Many thanks again.
Joelle

lucas
05-09-2007, 11:38 AM
Hello Steve,

It does work very well!
I sincerely appreciate your lasting patience with my quest, your several postbacks, and your precious time.

Many thanks again.
Joelle
Your very welcome Joelle....your response is what makes this worth doing...

Hope things have warmed up in La (if that's where your at). We were there for my son's wedding a week ago and we nearly froze...we had packed for a warm California.

joelle
05-09-2007, 01:01 PM
Steve,

It does a bit too much than "warmed up"
We have been getting a heat wave and today is just a bit better but Sunday and yesterday were cooking!!
How do you like LA - I'm not an LAer but my bros and sis live there. I find LA hasty and the folks there warm up easily. I live in the Silicon Valley, 1/2 hour from the San Francisco and I love this place. But I've never been to Oklahoma, some time in the future maybe ...

Thanks again Steve and enjoy your week.

Joelle

johnske
05-12-2007, 09:48 PM
Sorry I'm late on this one joelle, but I just answered a similar question on another board and then remembered this thread.

Here's another approach - it's fast and you can easily stipulate up to 30 criteria inside the OR statement...
Option Explicit

Sub CustomFilter_4Criteria()
Range("A1", Range("A" & Rows.Count).End(xlUp).Address).Offset(0, 1).Formula = _
"=IF(OR(AND(ISNUMBER(A1),A1>0),A1="">"",A1=""&"",A1=""*""),TRUE,"""")"
On Error Resume Next
With Columns(2)
.SpecialCells(xlCellTypeFormulas, xlTextValues).EntireRow.Hidden = True
.ClearContents
End With
End Sub

Sub UnhideRows()
ActiveSheet.Cells.EntireRow.Hidden = False
End Sub

lucas
05-12-2007, 11:15 PM
Nice VBA solution John..

joelle
05-14-2007, 08:52 AM
Hi John,
Hope your weekend was great - mine was okay but without internet (awful nowdays without it).
So, I saw your msg at work this morning - thank you for the codes and the attachment. I plugged the codes in and they work great!
Many thanks again.
Joelle