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

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

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?

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

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

Done

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

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