Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Essex, England
    Posts
    177
    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 great many people will think they are thinking when they are merely rearranging their prejudices." - William James

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 30 Times in 30 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
    177
    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 great many people will think they are thinking when they are merely rearranging their prejudices." - William James

Posting Permissions

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