+ Reply to Thread
Results 1 to 9 of 9

Advanced filter - multiple negative string match ORs???

  1. #1
    Registered User
    Join Date
    06-12-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    5

    Unhappy Advanced filter - multiple negative string match ORs???

    Hi folks - been lurking here anonymously a while.

    Gotta stumper I can't see solved anywhere else.

    Trying to filter out any address line 1 values in this spreadsheet that DON'T begin with a street number (for obvious reasons - they're bogus/unusable)/

    I tried adding a multi-row advanced filter criteria set to exclude any value that doesn't begin with 1, 2, 3, 4, 5, 6, 7 or 9:

    <>1*
    <>2*
    <>3*

    etc.

    Weird results - one criteria (e.g. just the <>1* works fine).

    Trying to use all 9 criteria does nothing.

    Here's the file
    data_test.xls

    Help?

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,183

    Re: Advanced filter - multiple negative string match ORs???

    A quick way would be just add a helper column then use

    =ISNUMBER(LEFT(I13,1)*1)

    TRUE for a number, FALSE no
    Last edited by jeffreybrown; 06-12-2013 at 10:04 PM.
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    06-12-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Advanced filter - multiple negative string match ORs???

    OK - forgive me for asking the next step, but I've spent 20 minutes googling "helper column + advanced filter" and similar terms, and I can't piece this together to make it work.

    Awesome that this formula correctly evaluates the address line "begins with number" condition, but how does advanced filter work with this helper column?

    Thank you!!!!

  4. #4
    Registered User
    Join Date
    06-12-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Advanced filter - multiple negative string match ORs???

    Nevermind!

    1. Create the column
    2. Drag it all the way down
    3. Do regular filter on TRUE or FALSE

    THANKS!

  5. #5
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,183

    Re: Advanced filter - multiple negative string match ORs???

    By a helper column I just mean, find a empty column and use it.

    In this case, in S12 place a header, in S13 put in the formula and copy it down to the end of your data.

    Now turn on the filters and then filter on S12 by either TRUE or FALSE
    Attached Files Attached Files

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,183

    Re: Advanced filter - multiple negative string match ORs???

    You're welcome…glad you have a workable solution and thanks for the feedback...

    Please consider marking this thread as solved if this resolves your issue.

  7. #7
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    Re: Advanced filter - multiple negative string match ORs???

    hi softwaresure, welcome to the forum. i think that was what Jeffrey was saying. anyway, here's a solution for Advanced Filter. i like to use a formula instead of multiple rows of criteria. to do that, place this formula in say cell T2:
    =OR(ISERR(LEFT(I13)+0),LEFT(I13)={"0","8"})

    ensure T1 has no header. then do the Advanced Filter:
    List Range:
    $A$12:$R$34

    Criteria Range:
    Donations!$T$1:$T$2

    i noticed you miss out the number 8 & 0, so i followed suit. if it's supposed to be factored in, you can use Jeffrey's formula in T2 too. Do mark it as "Solved" if nothing else
    Last edited by benishiryo; 06-12-2013 at 10:42 PM. Reason: amendment

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  8. #8
    Registered User
    Join Date
    06-12-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Advanced filter - multiple negative string match ORs???

    Solved twice, indeed!

    Thanks!!!!

  9. #9
    Registered User
    Join Date
    06-12-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Advanced filter - multiple negative string match ORs???

    One last question.

    I want to INCLUDE addresses that begin with a "P.O. Box" as valid.

    Can't quite get the formula to work for this advanced filter - any tips here?

    =OR(ISERR(LEFT(I5)+0),LEFT(I5)={"0","8"},AND(LEFT(I5)="P",LEFT(I6)=".",LEFT(I7)="O",LEFT(I7)="."))

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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