Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Using Data/Filter/Auto Filter

    When I click the dropdown it offers all of the data in the column as well as "Custom", which
    brings up the Custom Autofilter dialog box.

    This only offers 2 text boxes for criteria (and/or).

    Is there a way to have more than two criteria?

    Thank you !

    Michael Abrams

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you need more than AutoFilter offers, you can use Advanced Filter. This lets you set up a range for your selection criteria; this range can be as large as you like (within reasonable limits).

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    An alternate to Hans' recommendation of adv filter, you could create one (or more depending on complexity) columns that check the criteria (you can use one column with AND or ORs or use multiple ones based on some criterion) then you can use autofilter on these columns to simulate the multiple ones on a particular column. You filter on True or False

    This works best if you are doing the same type of filtering all the time since they tend ot be "more unique" and less flexible than the actual autofilter. There are some general utility ones (Matching multiple ORs from a list can be done with checking for MATCH from a prepared list, for example). Find/Search can be done for "contains", etc

    It all depends on your needs...

    Steve

  4. #4
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks guys - I will try both methods.

    The actual scenario is that I have spreadsheet with 50,000 records.

    Column B is filled in by a rep with 1 of 7 different choices.

    I am able to use the Autofilter on Column B to filter: "equals" choice 1 or equals choice 2 (for example)

    I would like to able to filter: equals choice 1 or equals choice 2 or equals choice 3.

    The Autofilter only allows two "ors", not three.

    Michael

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    As mentioned above, you could add a calculated column, with a formula such as

    =OR(B2="Choice1",B2="Choice2",B2="Choice3")

    in row 2, and filled down as far as needed. The column will display TRUE/FALSE values.

    Add an appropriate column header, then turn AutoFilter off and on again so that the new column is included.
    Select TRUE from the AutoFilter dropdown for this column.

    Or, with 50,000 records - use Access instead of Excel!

  6. #6
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I couldn't get the right syntax but what you offered worked fine !
    =OR(B2="Choice1",B2="Choice2",B2="Choice3")

    Thank you Hans !

    Michael

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Another option I alluded to, especially with a large or variable list, is to put the choices in a range (eg $F$1:$F$10, could even be on a different sheet if desired)

    And then use
    =ISNUMBER(MATCH(B2,$F$1:$F$10,0))

    and copy it down the column.

    You can control which are chosen by adding or removing from the range in F1:F10 (the list can contain blanks) without needing to change the formulas being filtered...

    Steve

  8. #8
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Steve - That one is pretty cool too. I'll give it a shot.

    Thank you very much !!

    Michael

Posting Permissions

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