Advanced Filter Magic by David Hager

 

Previously, I discussed the use of a custom list with Data Advanced Filter.

https://dhexcel1.wordpress.com/2017/04/14/excel-using-advanced-filter-with-a-custom-list-by-david-hager/

In the article, I only showed one custom list being used in the advanced filter. However, in most situations, a number of specific searches are required. To illustrate this, I combined all of the links from ESL #1-60 in an Excel file. The description of the links are in column A and the links are in column B. The goal is to perform multiple searches with lists while using only a single formula in the advanced filter. A picture of this can be seen in the following figure.

 MAF3

The formula in the advanced filter (B1:B2) on the AdvFilterTable tab is

=SUMPRODUCT(NOT(ISERROR(SEARCH(Choice,A5)))+0)>0

where Choice=CHOOSE(MATCH(AdvFilterTable!$A$2,CritLists,0),PowerBIList,VBAList,ExcelList)

This is the key formula for returning the desired list into the advanced filter formula.It is controlled by cell A2, which uses Data Validation with the following list.

CritLists=LList!$E$2:$E$4

The criteria lists tied to the CHOOSE function are are shown in the following figure.

 MAF2

These are expanding dynamic lists to add additional criteria if necessary.

PowerBIList=OFFSET(LList!$A$1,1,,COUNTA(LList!$A:$A)-1)

VBAList =OFFSET(LList!$B$1,1,,COUNTA(LList!$B:$B)-1)

ExcelList =OFFSET(LList!$C$1,1,,COUNTA(LList!$C:$C)-1)

When a list is selected in cell A2 and the advanced filter is applied to the links table, the result is as shown below.

 MAF1

Functionally, you can search all of my links for the content you desire. Also, you can use this filter model with your own data and criteria. I hope that this is useful.

You can download the file here.

ESL1-60_Filter

 

Advertisements

3 thoughts on “Advanced Filter Magic by David Hager

  1. Pingback: #Excel Super Links #82 – shared by David Hager | Excel For You

  2. Pingback: #Excel Super Links #120 – shared by David Hager | Excel For You

  3. Pingback: #Excel Super Links #148 – Shared by David Hager | Excel For You

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s