ADVANCED FILTER MULTIPLE CRITERIA MULTIPLE VALUE

[RPP]

Hi everybody

This is my first post here. I´m not an English native speaker but I´m gonna do the best I can.

This is the situation:

I found this article (http://chandoo.org/wp/2012/11/27/extract-subset-of-data/) about a macro that help us to make an advanced filter with multiple criteria. It works pretty well.

The problem I have is that I need to put multiple values per criteria.

I don´t know how to do it. So, now I'm here asking for your help.

Thanks a lot.

RPP

There is an attached file about the macro.
__________________________________________________________________

Mod edit : thread moved to appropriate forum !​

 

[Hui]

RPP

Firstly, Welcome to the Chandoo.org Forums

I think your confused as to how to apply filters

First there are a total of 14,832 records in Raw Data

If I apply the first field Filter of Product shown simply as



It retrieves all 14,832 records as expected there are no other records that dont match that list

So for the purposes of Filtering, you can leave that list out

If I want all records from a Field that doesn't need filtering

If I want all the fields that just match west
Then I would setup the Advanced Filter as shown below


And then setup the Advanced Filter using:


Note d is a Named Formula referring to =Table1[#All]

Applying this lists all records for all fields where Region is West only

 

[Hui]

If you want to filter Region = West or Region = North
you would apply the same using:

But set the Criteria Range as B1:B3

 

[Hui]

So to develop a complex filter consider that each Row in the criteria is an Or statement, but each Field in the Row is an And statement

eg:


The above says

Filter the table where Region = West and Cust_Type=Individual or Region = North and Cust_Type=SME

You should now be able to understand why your previous filter didn't work

I'd recommend reading:
http://www.contextures.com/xladvfilter01.html
then
http://www.contextures.com/xladvfilter02.html