+ Reply to Thread
Results 1 to 8 of 8

filter by more than 2 criteria

  1. 09-01-2005, 03:05 PM #1
    Alex
    Guest

    filter by more than 2 criteria

    I'm applying the following filtering:
    Worksheets("Sheet1").Select

    Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _
    Criteria2:="=PM" ' Working well

    But, I need more criterias there such as:

    Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _
    Criteria2:="=PM" ', Operator:=xlOr, Criteria3:="=HPW" , _
    Operator:=xlOr, Criteria4:="=OJT", _
    Operator:=xlOr, Criteria5:="=SBR", _
    Operator:=xlOr, Criteria6:="=LD", _
    Operator:=xlOr, Criteria7:="=CFT"

    How could I do it?

    Thanks

    Reply With QuoteRegister To Reply
  2. 09-01-2005, 04:05 PM #2
    Ron de Bruin
    Guest

    Re: filter by more than 2 criteria

    Hi Alex

    You can use Advanced Filter to do this
    http://www.contextures.com/xladvfilter01.html

    Or insert a helper column and AutoFilter on that column
    =OR(A1={"A","B","C","D","E"})

    EasyFilter have 5 options
    http://www.rondebruin.nl/easyfilter.htm

    --
    Regards Ron de Bruin
    http://www.rondebruin.nl


    "Alex" <[email protected]> wrote in message news:[email protected]...
    > I'm applying the following filtering:
    > Worksheets("Sheet1").Select
    >
    > Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _
    > Criteria2:="=PM" ' Working well
    >
    > But, I need more criterias there such as:
    >
    > Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _
    > Criteria2:="=PM" ', Operator:=xlOr, Criteria3:="=HPW" , _
    > Operator:=xlOr, Criteria4:="=OJT", _
    > Operator:=xlOr, Criteria5:="=SBR", _
    > Operator:=xlOr, Criteria6:="=LD", _
    > Operator:=xlOr, Criteria7:="=CFT"
    >
    > How could I do it?
    >
    > Thanks
    >


    Reply With QuoteRegister To Reply
  3. 09-01-2005, 04:05 PM #3
    Bill Martin -- (Remove NOSPAM from address)
    Guest

    Re: filter by more than 2 criteria

    Alex wrote:
    > I'm applying the following filtering:
    > Worksheets("Sheet1").Select
    >
    > Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _
    > Criteria2:="=PM" ' Working well
    >
    > But, I need more criterias there such as:
    >
    > Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _
    > Criteria2:="=PM" ', Operator:=xlOr, Criteria3:="=HPW" , _
    > Operator:=xlOr, Criteria4:="=OJT", _
    > Operator:=xlOr, Criteria5:="=SBR", _
    > Operator:=xlOr, Criteria6:="=LD", _
    > Operator:=xlOr, Criteria7:="=CFT"
    >
    > How could I do it?
    >
    > Thanks
    >


    The way it's been done since the beginning of computer science is to sort on the
    least significant field first. Then do it again with the next least, and so
    forth until you're done. This is pretty much what any sorting software you'll
    find will do under the covers to handle multiple keys.

    Bill
    Reply With QuoteRegister To Reply
  4. 09-01-2005, 04:05 PM #4
    Alex
    Guest

    Re: filter by more than 2 criteria

    Thanks a lot, Ron.

    Could you please explain how to create this helper column.

    "Ron de Bruin" wrote:

    > Hi Alex
    >
    > You can use Advanced Filter to do this
    > http://www.contextures.com/xladvfilter01.html
    >
    > Or insert a helper column and AutoFilter on that column
    > =OR(A1={"A","B","C","D","E"})
    >
    > EasyFilter have 5 options
    > http://www.rondebruin.nl/easyfilter.htm
    >
    > --
    > Regards Ron de Bruin
    > http://www.rondebruin.nl
    >
    >
    > "Alex" <[email protected]> wrote in message news:[email protected]...
    > > I'm applying the following filtering:
    > > Worksheets("Sheet1").Select
    > >
    > > Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _
    > > Criteria2:="=PM" ' Working well
    > >
    > > But, I need more criterias there such as:
    > >
    > > Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _
    > > Criteria2:="=PM" ', Operator:=xlOr, Criteria3:="=HPW" , _
    > > Operator:=xlOr, Criteria4:="=OJT", _
    > > Operator:=xlOr, Criteria5:="=SBR", _
    > > Operator:=xlOr, Criteria6:="=LD", _
    > > Operator:=xlOr, Criteria7:="=CFT"
    > >
    > > How could I do it?
    > >
    > > Thanks
    > >
    >
    >
    >
    Reply With QuoteRegister To Reply
  5. 09-01-2005, 04:05 PM #5
    Ron de Bruin
    Guest

    Re: filter by more than 2 criteria

    Hi Alex

    For example your data range is A1:G100
    Row 1 are headers and you want to check A2:A100 for the Criteria

    In H1 enter header text
    In H2 copy this formula and copy it down to H100
    =OR(A2={"ERP","PM","HPW","OJT","SBR"","LD","CFT"})

    Now you can use AutoFilter on column H for True or False


    --
    Regards Ron de Bruin
    http://www.rondebruin.nl


    "Alex" <[email protected]> wrote in message news:[email protected]...
    > Thanks a lot, Ron.
    >
    > Could you please explain how to create this helper column.
    >
    > "Ron de Bruin" wrote:
    >
    >> Hi Alex
    >>
    >> You can use Advanced Filter to do this
    >> http://www.contextures.com/xladvfilter01.html
    >>
    >> Or insert a helper column and AutoFilter on that column
    >> =OR(A1={"A","B","C","D","E"})
    >>
    >> EasyFilter have 5 options
    >> http://www.rondebruin.nl/easyfilter.htm
    >>
    >> --
    >> Regards Ron de Bruin
    >> http://www.rondebruin.nl
    >>
    >>
    >> "Alex" <[email protected]> wrote in message news:[email protected]...
    >> > I'm applying the following filtering:
    >> > Worksheets("Sheet1").Select
    >> >
    >> > Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _
    >> > Criteria2:="=PM" ' Working well
    >> >
    >> > But, I need more criterias there such as:
    >> >
    >> > Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _
    >> > Criteria2:="=PM" ', Operator:=xlOr, Criteria3:="=HPW" , _
    >> > Operator:=xlOr, Criteria4:="=OJT", _
    >> > Operator:=xlOr, Criteria5:="=SBR", _
    >> > Operator:=xlOr, Criteria6:="=LD", _
    >> > Operator:=xlOr, Criteria7:="=CFT"
    >> >
    >> > How could I do it?
    >> >
    >> > Thanks
    >> >
    >>
    >>
    >>


    Reply With QuoteRegister To Reply
  6. 09-01-2005, 06:05 PM #6
    Ron de Bruin
    Guest

    Re: filter by more than 2 criteria

    Hi Alex

    I have a typo in the formula, this one is OK (one " to much in the other one)

    =OR(A2={"ERP","PM","HPW","OJT","SBR","LD","CFT"})



    --
    Regards Ron de Bruin
    http://www.rondebruin.nl


    "Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
    > Hi Alex
    >
    > For example your data range is A1:G100
    > Row 1 are headers and you want to check A2:A100 for the Criteria
    >
    > In H1 enter header text
    > In H2 copy this formula and copy it down to H100
    > =OR(A2={"ERP","PM","HPW","OJT","SBR"","LD","CFT"})
    >
    > Now you can use AutoFilter on column H for True or False
    >
    >
    > --
    > Regards Ron de Bruin
    > http://www.rondebruin.nl
    >
    >
    > "Alex" <[email protected]> wrote in message news:[email protected]...
    >> Thanks a lot, Ron.
    >>
    >> Could you please explain how to create this helper column.
    >>
    >> "Ron de Bruin" wrote:
    >>
    >>> Hi Alex
    >>>
    >>> You can use Advanced Filter to do this
    >>> http://www.contextures.com/xladvfilter01.html
    >>>
    >>> Or insert a helper column and AutoFilter on that column
    >>> =OR(A1={"A","B","C","D","E"})
    >>>
    >>> EasyFilter have 5 options
    >>> http://www.rondebruin.nl/easyfilter.htm
    >>>
    >>> --
    >>> Regards Ron de Bruin
    >>> http://www.rondebruin.nl
    >>>
    >>>
    >>> "Alex" <[email protected]> wrote in message news:[email protected]...
    >>> > I'm applying the following filtering:
    >>> > Worksheets("Sheet1").Select
    >>> >
    >>> > Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _
    >>> > Criteria2:="=PM" ' Working well
    >>> >
    >>> > But, I need more criterias there such as:
    >>> >
    >>> > Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _
    >>> > Criteria2:="=PM" ', Operator:=xlOr, Criteria3:="=HPW" , _
    >>> > Operator:=xlOr, Criteria4:="=OJT", _
    >>> > Operator:=xlOr, Criteria5:="=SBR", _
    >>> > Operator:=xlOr, Criteria6:="=LD", _
    >>> > Operator:=xlOr, Criteria7:="=CFT"
    >>> >
    >>> > How could I do it?
    >>> >
    >>> > Thanks
    >>> >
    >>>
    >>>
    >>>
    >
    >


    Reply With QuoteRegister To Reply
  7. 09-01-2005, 06:05 PM #7
    Alex
    Guest

    Re: filter by more than 2 criteria

    Thank you very much, Ron.
    It should work but I just cannot handle with the formula. It says "The
    formula you typed contains an error". I've tried Ctrl+Shift+Enter but it
    doesn't help.

    "Ron de Bruin" wrote:

    > Hi Alex
    >
    > For example your data range is A1:G100
    > Row 1 are headers and you want to check A2:A100 for the Criteria
    >
    > In H1 enter header text
    > In H2 copy this formula and copy it down to H100
    > =OR(A2={"ERP","PM","HPW","OJT","SBR"","LD","CFT"})
    >
    > Now you can use AutoFilter on column H for True or False
    >
    >
    > --
    > Regards Ron de Bruin
    > http://www.rondebruin.nl
    >
    >
    > "Alex" <[email protected]> wrote in message news:[email protected]...
    > > Thanks a lot, Ron.
    > >
    > > Could you please explain how to create this helper column.
    > >
    > > "Ron de Bruin" wrote:
    > >
    > >> Hi Alex
    > >>
    > >> You can use Advanced Filter to do this
    > >> http://www.contextures.com/xladvfilter01.html
    > >>
    > >> Or insert a helper column and AutoFilter on that column
    > >> =OR(A1={"A","B","C","D","E"})
    > >>
    > >> EasyFilter have 5 options
    > >> http://www.rondebruin.nl/easyfilter.htm
    > >>
    > >> --
    > >> Regards Ron de Bruin
    > >> http://www.rondebruin.nl
    > >>
    > >>
    > >> "Alex" <[email protected]> wrote in message news:[email protected]...
    > >> > I'm applying the following filtering:
    > >> > Worksheets("Sheet1").Select
    > >> >
    > >> > Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _
    > >> > Criteria2:="=PM" ' Working well
    > >> >
    > >> > But, I need more criterias there such as:
    > >> >
    > >> > Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _
    > >> > Criteria2:="=PM" ', Operator:=xlOr, Criteria3:="=HPW" , _
    > >> > Operator:=xlOr, Criteria4:="=OJT", _
    > >> > Operator:=xlOr, Criteria5:="=SBR", _
    > >> > Operator:=xlOr, Criteria6:="=LD", _
    > >> > Operator:=xlOr, Criteria7:="=CFT"
    > >> >
    > >> > How could I do it?
    > >> >
    > >> > Thanks
    > >> >
    > >>
    > >>
    > >>
    >
    >
    >
    Reply With QuoteRegister To Reply
  8. 09-01-2005, 06:05 PM #8
    Alex
    Guest

    Re: filter by more than 2 criteria

    Thank you very much again, Ron.
    It's working just great.

    "Ron de Bruin" wrote:

    > Hi Alex
    >
    > For example your data range is A1:G100
    > Row 1 are headers and you want to check A2:A100 for the Criteria
    >
    > In H1 enter header text
    > In H2 copy this formula and copy it down to H100
    > =OR(A2={"ERP","PM","HPW","OJT","SBR"","LD","CFT"})
    >
    > Now you can use AutoFilter on column H for True or False
    >
    >
    > --
    > Regards Ron de Bruin
    > http://www.rondebruin.nl
    >
    >
    > "Alex" <[email protected]> wrote in message news:[email protected]...
    > > Thanks a lot, Ron.
    > >
    > > Could you please explain how to create this helper column.
    > >
    > > "Ron de Bruin" wrote:
    > >
    > >> Hi Alex
    > >>
    > >> You can use Advanced Filter to do this
    > >> http://www.contextures.com/xladvfilter01.html
    > >>
    > >> Or insert a helper column and AutoFilter on that column
    > >> =OR(A1={"A","B","C","D","E"})
    > >>
    > >> EasyFilter have 5 options
    > >> http://www.rondebruin.nl/easyfilter.htm
    > >>
    > >> --
    > >> Regards Ron de Bruin
    > >> http://www.rondebruin.nl
    > >>
    > >>
    > >> "Alex" <[email protected]> wrote in message news:[email protected]...
    > >> > I'm applying the following filtering:
    > >> > Worksheets("Sheet1").Select
    > >> >
    > >> > Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _
    > >> > Criteria2:="=PM" ' Working well
    > >> >
    > >> > But, I need more criterias there such as:
    > >> >
    > >> > Selection.AutoFilter Field:=1, Criteria1:="=ERP", Operator:=xlOr, _
    > >> > Criteria2:="=PM" ', Operator:=xlOr, Criteria3:="=HPW" , _
    > >> > Operator:=xlOr, Criteria4:="=OJT", _
    > >> > Operator:=xlOr, Criteria5:="=SBR", _
    > >> > Operator:=xlOr, Criteria6:="=LD", _
    > >> > Operator:=xlOr, Criteria7:="=CFT"
    > >> >
    > >> > How could I do it?
    > >> >
    > >> > Thanks
    > >> >
    > >>
    > >>
    > >>
    >
    >
    >
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1