+ Reply to Thread
Results 1 to 13 of 13

Search Query Filtering VBA Needed on Specific Excel

  1. #1
    Registered User
    Join Date
    08-10-2011
    Location
    Chicago, Illinois
    MS-Off Ver
    Excel 2010
    Posts
    16

    Search Query Filtering VBA Needed on Specific Excel

    Hi,

    I would like one page where I can specify certain criteria and return a list of all the rows that contain those criteria, essentially a large VBA Query Filtering masterpage.

    I've attached a scrubbed excel below (in a post below titled TEST3).

    Essentially you'll see 4 tabs, List1, List2, List3, and List4. Each list has the same columns but different rows with various types of info according to 4 sections of our business (again scrubbed).

    Essentially I would like the master sheet to have drop downs or checkoff lists that search List1, List2, List3, or List4 (and place that result in a new column), and then search a custom filter for Y, N, N/A, answers from Questions 1 to Questions 8, which are essentially column O to Column W (skipping column S) and then where you can simply click a button and it returns all the rows but only columns J to AE in that newly created master tab (along with a new column that shows List1, List2, List3 or List4 as a name. If this last part is too complex then we can remove.

    So we will want to filter based on data in Columns O-W, but the caveat is that we need OR and AND functions to be appropriately placed in the VBA/search criteria:
    o Y in either (O or T) AND Y in either (R or W)
    o Y in either (P or U) AND Y in either (R or W)
    o Y in either (O or T) AND N in either (R or W)
    o Y in either (P or U) AND N in either (R or W)
    o N in either (O or T) AND Y in either (R or W)
    o N in either (P or U) AND Y in either (R or W)
    o N in either (O or T) AND N in either (R or W)
    o N in either (P or U) AND N in either (R or W)

    So some kind of checkbox or dropdown that can account for filtering for these WITHOUT excluding them on accident.

    Best,



    Original Post:

    Hi,

    I'm trying to filter data based on OR and AND criteria. I thought AutoFilter would simply do it but it doesn't show what I need.

    To keep it simple:

    I have 10,000 rows of instances, and each instance has 4 columns with questions that answer either YES, NO, or MAYBE. I want to filter and show all the rows that say "YES" in columns 1 OR 2, and that say "NO" in columns 3 AND 4.

    Basically how do i incorporate the OR and AND functionality.

    Thanks
    Last edited by cnyoon2; 11-29-2013 at 11:06 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,883

    Re: Filtering Data with OR and AND criteria

    You cant really use the Filter function as-is for this, as soon as you filter A on "Yes", you may already be filtering out a few "Yes" in B etc.

    I would suggest a helper column that will return, say, "Yes" in col 1&2 and that say "No" in 3&4

    Maybe something like...
    =if(and(countif(A2:B2,"yes")>1,countif(C2:D2,"no")>1),"Yes","No")
    or...
    =if(and(or(A2="yes",B2="yes"),or(C2="no",D2="no"))"Yes","No")

    Then filter on the "Yes" in the helper
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    08-10-2011
    Location
    Chicago, Illinois
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Filtering Data with OR and AND criteria

    Would it be better if I tried to do a VBA on this? I will try to scrub a version and post it in a separate post.

  4. #4
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Filtering Data with OR and AND criteria

    you can use advanced filter but im not sure on your criteria. is that col 1 or 2 is yes and cols 3&4 both =no?
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,883

    Re: Filtering Data with OR and AND criteria

    VBA is always an option, but seeing some sample data will probably help a lot

  6. #6
    Registered User
    Join Date
    08-10-2011
    Location
    Chicago, Illinois
    MS-Off Ver
    Excel 2010
    Posts
    16

    Search Query Filtering VBA Needed on Specific Excel

    Hi,

    I would like one page where I can specify certain criteria and return a list of all the rows that contain those criteria, essentially a large VBA Query Filtering masterpage.

    I've attached a scrubbed excel below.

    Essentially you'll see 4 tabs, List1, List2, List3, and List4. Each list has the same columns but different rows with various types of info according to 4 sections of our business (again scrubbed).

    Essentially I would like the master sheet to have drop downs or checkoff lists that search List1, List2, List3, or List4 (and place that result in a new column), and then search a custom filter for Y, N, N/A, answers from Questions 1 to Questions 8, which are essentially column O to Column W (skipping column S) and then where you can simply click a button and it returns all the rows but only columns J to AE in that newly created master tab (along with a new column that shows List1, List2, List3 or List4 as a name. If this last part is too complex then we can remove.

    So we will want to filter based on data in Columns O-W, but the caveat is that we need OR and AND functions to be appropriately placed in the VBA/search criteria:
    o Y in either (O or T) AND Y in either (R or W)
    o Y in either (P or U) AND Y in either (R or W)
    o Y in either (O or T) AND N in either (R or W)
    o Y in either (P or U) AND N in either (R or W)
    o N in either (O or T) AND Y in either (R or W)
    o N in either (P or U) AND Y in either (R or W)
    o N in either (O or T) AND N in either (R or W)
    o N in either (P or U) AND N in either (R or W)

    So some kind of checkbox or dropdown that can account for filtering for these WITHOUT excluding them on accident.

    Best,
    Chris
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    08-10-2011
    Location
    Chicago, Illinois
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Filtering Data with OR and AND criteria

    Ok, I made a new post in the VBA section, this includes the same ask but with a VBA mastersheet that would be better for us I think.

    Linked here:
    http://www.excelforum.com/excel-prog...fic-excel.html

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,883

    Re: Filtering Data with OR and AND criteria

    I think it would be better to keep them together, so I will merge your other thread into this 1, just to keep things tidy

  9. #9
    Registered User
    Join Date
    08-10-2011
    Location
    Chicago, Illinois
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Filtering Data with OR and AND criteria

    ok no problem, could you replace the name of the thread with the "Search Query Filtering VBA Needed on Specific Excel" I had?

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,883

    Re: Search Query Filtering VBA Needed on Specific Excel

    Done

    For future reference, you can change your own titles, click EDIT on you're 1st post, then Go Advanced and change your title

  11. #11
    Registered User
    Join Date
    08-10-2011
    Location
    Chicago, Illinois
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Search Query Filtering VBA Needed on Specific Excel

    I'm a noob so sorry haha i will do that next time, or better yet just try to make a post the first time around that would do the same thing...

    Anyway, please, let me know if my ask isn't clear, I can try to explain again.

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,883

    Re: Search Query Filtering VBA Needed on Specific Excel

    Hey, we all have to learn some time, so no problem

  13. #13
    Registered User
    Join Date
    08-10-2011
    Location
    Chicago, Illinois
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Search Query Filtering VBA Needed on Specific Excel

    Anyone have an idea on this? Still need help! thanks!

+ 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. Multiple criteria filtering of data
    By penfold1992 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-09-2012, 05:07 AM
  2. Filtering data according to 2 criteria within rows
    By grafx77 in forum Excel General
    Replies: 4
    Last Post: 07-06-2012, 05:50 PM
  3. Filtering data based on criteria
    By rhudgins in forum Excel General
    Replies: 6
    Last Post: 04-15-2010, 02:03 PM
  4. Multiple Criteria Data Filtering
    By croyer in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-10-2008, 06:44 PM
  5. Filtering data by a Criteria column
    By v282788 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-18-2008, 02:28 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