Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Essex, England
    Posts
    175
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Exclusion Filtering using the Advanced Filter tool (Excel 2003)

    I have a series of macros set up in a worksheet that filter data using the Advanced Filter feature in Excel 2003. The filter criteria are on a hidden worksheet and they work well. However, I want to set up a sort of anti-filter that will show me everything but the data that matches one of the criteria sets.

    The criteria defined consists of three textual data items, (although a couple have wildcards) from a list of many that is expanding as time goes on. If I created a criteria list that explicitly included everything but the first set, I
    Regards,

    Steve

    "A good friend will help you move; a really good friend will help you move a body"

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Exclusion Filtering using the Advanced Filter tool (Excel 2003)

    Say that the field on which you want to filter is in column A on a sheet imaginatively named Sheet (A1 is the field name).
    The three values you want to exclude are in Criteria!C1:C3.
    The criteria range is Criteria!A1:A2
    A1 is intentionally left blank.
    A2 contains the formula =ISNA(MATCH(Sheet!A2,$C$1:$C$3,0))
    This formula refers to the first data row in the data table.

    See attached workbook.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Exclusion Filtering using the Advanced Filter tool (Excel 2003)

    You can get the "excluding" list by changing the criteria.

    Suppose you have:

    Heading 1
    test
    Other Test

    Then use this to get the opposite:

    Heading 1
    ="<>test"
    ="<>Other Test"
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Essex, England
    Posts
    175
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Exclusion Filtering using the Advanced Filter tool (Excel 2003)

    Thanks for your speedy replies, chaps.

    Hans' answer works but I find it a little non-intuitive - I had to look at the formula and move about between the sheets before I could work out what it was doing.

    Jan Karel's answer works, but only after I'd transposed the original criteria column into an "anti-criteria" row. Creating a column the way he suggests is certainly easier to understand but the need to use AND rather than OR to group the criteria meant that I had to make the column a row. One of the reasons my earlier attempts failed was because I'd kept the columnar arrangement of the criteria.

    I knew it would be fairly obvious when the penny finally dropped.

    Thanks again, gentlemen.
    Regards,

    Steve

    "A good friend will help you move; a really good friend will help you move a body"

Posting Permissions

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