#Excel: Using Advanced Filter with a Custom List By David Hager

7 Replies

In a previous article about Excel’s advanced filter,

https://dhexcel1.wordpress.com/2017/03/25/using-the-excel-advanced-filter-to-a-list-having-internal-numbers-in-a-string-by-david-hager/

I used a reference to Charley Kyd’s use of SUMPRODUCT to filter a list based on a custom list.

http://www.exceluser.com/formulas/search-list-with-sumproduct.htm

In his article, he created a formula used in a helper column to filter based on a custom list of criterias.

I realized that the same type of formula could be used in Excel’s advanced filter. A lot of the utility of using the Excel advanced filter feature is hindered by the fact that an array formula cannot be used as a criteria. However, since the SUMPRODUCT function creates a non-array formula, I surmized that it could be used as a criteria.

BTW, Rob Collie and I published an article on this same subject in PowerPivot a few years ago.

https://powerpivotpro.com/2011/04/calculating-a-sum-based-on-a-list-criteria/

In this scenario, a list containing a column of employee numbers needs to be filtered by a custom subset of those numbers. This could be done by manually selecting those numbers from the filter dropdown criteria, which would take a very long time for a large custom list. The custom list resides on a different worksheet than the list to be filtered (in this case, on the List worksheet defined as EmployeeList).

The criteria formula (in B2) is:

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

Note that this formula “starts” at A5, the first item in the column.

Before applying the filter, the column looks like this:

 MultiAdv1

The advanced filter information is set up as shown below.

 MultiAdv2

Finally, the list after filtering looks like this:

 MultiAdv3

In this example, there is only one column in the list/table. Obviously an employee table would contain many more columns. Also, the employee custom list could potentially could contain 1000’s of employee numbers. I hope that you use this technique useful in your work on employee records.

You can download the example file here.

MultAdvFilter