Results 1 to 3 of 3
  1. #1
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    220
    Thanks
    13
    Thanked 0 Times in 0 Posts

    Can I create a "not" criteria range in Excel 2010?

    I want to filter a list of names and find the names that do not meet a list of names in my criteria field. I can do this by filtering for names that are in the list, deleting those, and then clearing the filter. Is there a better way? Thanks.
    JimmyW
    Helena, MT

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Jimmy,

    One approach would be to add a column next to the Names you want filter with this formula:
    =ISNA(MATCH(A2,Criteria,0)) then filter for True.
    Where Criteria is your Filter List {located on Sheet2 in att. example}.
    Attached Images Attached Images
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Jimmy-W (2011-12-09)

  4. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    FYI, you could also use the same criterion formula for an advanced filter without adding the extra column.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. The Following User Says Thank You to rory For This Useful Post:

    Jimmy-W (2011-12-09)

Posting Permissions

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