+ Reply to Thread
Results 1 to 11 of 11

Advanced Filter, and multiple <> criteria

  1. #1
    Registered User
    Join Date
    02-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    13

    Exclamation Advanced Filter, and multiple <> criteria

    Hello,

    I have a large email list, I have attached an example. From the email list I have separated the address prefix and the domain suffix with the delimit function.
    [email protected] = lol and gmail.com in separate columns, but on the same row.

    Many of the emails have useless addresses, for example: info, wineclub, etc. I would like to filter my list such that these useless address prefixes exclude the email from the table.

    I have tried using the <>info and <>wineclub commands, but every time I try to filter with multiple <> criteria, excel fails to filter any of the data.

    Have I structured my logic wrong? What is wrong?
    Sonoma_Test1.xlsx

    Please help.

  2. #2
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: Advanced Filter, and multiple <> criteria

    Cannot replicate. Attached file contains filter excluding "wineclub" and "info" email addresses.
    Attached Files Attached Files

  3. #3
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Advanced Filter, and multiple <> criteria

    Hi SILENTBRONCO

    Here's a different approach.

    Notice the Dynamic Named Range (BadAddress) in Sheet Filter. CTRL + x will fire the Code.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  4. #4
    Registered User
    Join Date
    02-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    13

    Re: Advanced Filter, and multiple <> criteria

    Jaslake, your code works very well, thank you. But, is it possible to do exactly the same thing without the need for the macro?

    Also, where can I learn to code like this, what type of code is this?

  5. #5
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Advanced Filter, and multiple <> criteria

    Hi SILENTBRONCO

    The Code is Visual Basic (VBA) and, I think, is available in all Office Applications.

    Where can you learn it? Google is your Best Friend. Also, John Walkenbach's stuff is very good.

    Regarding this...I'd assume so...I'd not wish to tackle the mind boggling Formulas that may be required.

    is it possible to do exactly the same thing without the need for the macro

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Advanced Filter, and multiple <> criteria

    Try this.

    Clear the existing criteria, enter this formula in A2,
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and use A1:A2 as the criteria for the advanced filter.
    If posting code please use code tags, see here.

  7. #7
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Advanced Filter, and multiple <> criteria

    Anybody want to tell me if this worked?

  8. #8
    Registered User
    Join Date
    02-11-2013
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    13

    Re: Advanced Filter, and multiple <> criteria

    Norie, doesn't seem like it worked. After inputting the equation, all the emails were filtered. except for [email protected]

    I dont seem to be able to solve this. Only the VBA code has worked so far.

  9. #9
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Advanced Filter, and multiple <> criteria

    What exactly did you try?

  10. #10
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Advanced Filter, and multiple <> criteria

    In A2 enter this formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Copy the heading in A1 to C1 and enter this formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    In the criteria be sure to select A1:C2

    A
    B
    C
    1
    Delimited_Address Delimited_Domain Delimited_Address
    2
    <>info <>wineclub
    Last edited by newdoverman; 10-11-2015 at 03:24 PM.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  11. #11
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Advanced Filter, and multiple <> criteria

    SILENTBRONCO

    Did you completely clear out the criteria leaving A1 blank and A2 with the formula I posted?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 09-15-2012, 08:00 AM
  2. Advanced filter using multiple criteria
    By logan131131 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-16-2012, 11:36 AM
  3. [SOLVED] Advanced Filter: Problem with Multiple AND/OR Criteria
    By excelnewbie80 in forum Excel General
    Replies: 2
    Last Post: 06-18-2012, 09:00 AM
  4. Advanced Filter with multiple criteria
    By inspiresit in forum Excel General
    Replies: 6
    Last Post: 08-12-2010, 11:21 AM
  5. advanced filter through vba with multiple criteria
    By edoylederry in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-08-2010, 07:23 AM
  6. Advanced filter on multiple criteria
    By agalloch in forum Excel General
    Replies: 2
    Last Post: 08-19-2009, 09:48 AM
  7. [SOLVED] advanced filter macro to locate values via multiple criteria
    By jjfjr in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-07-2005, 11:35 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1