Tag Archives: filter

#Excel Short and Sweep Tip #13 (KeyBoard DFAROT Unique Technique) by David Hager

Using Data Filter Advanced Filter to obtain a list of unique items from another list is quick and relatively easy to do. However, when Rob van Gelder came up with a keystroke way to do this rather by clicking, it made this a lot easier to accomplish.

http://dailydoseofexcel.com/archives/2012/05/25/copy-unique-values/

as per his instructions:

Select the range to extract from

Hold down the Alt key

Press these keys in sequence: d, f, a, r, o, t

Release the Alt key

Select the range to paste the unique values to.

The only caveat to this approach that I found is that when Excel does not recognize that the selection has headers, it will stop at an intermediary stage. Then, you can click the appropriate options to finish the unique items copy.

HTH!

#Excel: Using Conditional Formatting to Highlight Cells Containing Native 3D Formulas by David Hager

Conditional formatting (CF) in Excel can be used to hightlight cells that meet certain criteria. In this case, I wanted to create a CF that would highlight cells containing formulas that use Excel’s native 3D references. So, this would be like the following example.

=SUM(Sheet1:Sheet2!B2:B5)

So, I tried to determine what was unique this type of formula string compared to others. What I noticed was that the first colon in this formula always comes before the exclamation point. Thus, I started working on a solution on that basis.

Note, though, that there are ways to write a formula containing a 3D reference that will not meet this criteria, such as:

=SUM(C2:C5,Sheet2!C2:C5)

So, don’t use those kinds of formulas. 😊

To lookup the position of the colon in the formula string, the following formula is needed.

=MATCH(“:”,MID(FORMULATEXT(F6),ROW(INDIRECT(“1:”&LEN(FORMULATEXT(F6)))),1),0)

where F6 contains the formula.

The corresponding formula for looking up the position of the exclamation point is:

=MATCH(“!”,MID(FORMULATEXT(F6),ROW(INDIRECT(“1:”&LEN(FORMULATEXT(F6)))),1),0)

By comparing the two formulas, the following Boolean expression wrapped in an IFERROR function is defined as Is3D:

=IFERROR(MATCH(“:”,MID(FORMULATEXT(F6),ROW(INDIRECT(“1:”&LEN(FORMULATEXT(F6)))),1),0)<=MATCH(“!”,MID(FORMULATEXT(F6),ROW(INDIRECT(“1:”&LEN(FORMULATEXT(F6)))),1),0),FALSE)

Applying this formula as a CF on cell F6, you can see that F6 is highlighted as expected.

Is3D1

You can download the example file here.

IsNative3DFormula

#Excel: Four Super Filter Techniques by David Hager

I wanted to improve a few design features and eliminate a few bugs in some of my related recent posts,

so I have combined the concepts demonstrated by the following 4 articles located at:

https://dhexcel1.wordpress.com/2017/03/30/excel-filtered-list-using-a-calculated-column-to-count-occurrence-number-by-david-hager/

https://dhexcel1.wordpress.com/2017/03/30/excel-creating-a-list-made-up-of-formulas-from-a-filtered-list-by-david-hager/

https://dhexcel1.wordpress.com/2017/04/06/using-excel-vba-to-create-a-filter-criteria-function-by-david-hager/

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

into a single workbook. Among the changes I did/did not make are:

  1. No change to the formula that given occurance number for records in a filtered list.
  2. I moved the formula list from above the filtered list to another worksheet.
  3. I fixed some issues with the VBA code for the Filter Criteria UDF. I then located it with the
  4. the static list on the other worksheet.
  5. I included the Advanced Filter search with a custom list with this model.

In moving the static calculated list to another worksheet, I had significant problems adjusting the formulas to work in their new location. For the formula:
=INDIRECT(“Sheet1!a”&SMALL(IF(SUBTOTAL(3,OFFSET(Sheet1!$C$24:$C5023,ROW($C$24:$C5023)-MIN(ROW($C$24:$C5023)),,1)),ROW(1:5000),””),ROW()-2)+26-ROW())

I had to add the sheets names in the parts of the formula that point to the filtered list on Sheet1. Also, to create the correct positioning of the first formula(s) in the list (place on row 3 instead of row 2), I had to change:

ROW()-1)+25-ROW())

to

ROW()-2)+26-ROW())

The FilterCriteriaEnh function was amended to fix several faults – an error handler was added to fix the scenario where .Criteria2 did not exist and adding a line of code (Criteria2=.Criteria2) in case it did (see code in the example workbook).

The criteria UDF was relocated to the row above the static list. The following formula in A1 on the Static worksheet is:

=IFERROR(FilterCriteriaEnh(Sheet1!A23:A5023),””)

Note that the range in the UDF argument points at the filtered list on Sheet1.

aaRefine1

The list used for the advanced filter lookup is also located on the Static worksheet. As expected, when I activated the advanced filter the filter criteria UDF did not return a result, since no “filter” was applied to the list.

I hope that I have explained what is available with this new filter model, but if not, please go back and reread the 4 base articles.

You can download the (enhanced) file here.

FilteredResultsTable_AdvancedFilter

 

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

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

 

Using the SUBTOTAL Function in #Excel To Aggregate Values From Multiple Filtered Lists by David Hager

So, imagine a scenario where 4 filtered lists reside in the same position on adjacent worksheets that have “sequential” names. Perhaps each list comes from a monthly summary, but filtering is applied differently for each list. Well, I did not build that example, but instead have 4 sheets named A to D, a list on each sheet residing in the same range and each column filled with numbers from 0 to 99. The object is to perform aggregations on the filtered data from all sheets at once (or, creating a 3D formula).

Actually, I have already written a blog post about this subject, but I felt that it would be useful to cover this again in an expanded version and provide a workbook to download.

https://dhexcel1.wordpress.com/2014/06/13/using-excel-3d-formulas-on-filtered-lists/

For examples on various types of “multi-worksheet” formulas, see:

https://dhexcel1.wordpress.com/2014/06/09/excel-3d-easy-as-1-2-3-and-a-b-c-and-others/

The figures below shows the results of the 2 types of 3D formulas.

XL3DST1XL3DST2

The filter-enabled formula shown below sums the filtered values in the 4 filtered lists. Note that since the SUMPRODUCT function is used here, the result is NOT an array formula.

=SUMPRODUCT(SUBTOTAL(9,INDIRECT(“‘”&CHAR(ROW($97:$100))&”‘!C2:C21”)))

The native Excel method for creating a 3D formula is shown here.

=SUM(A:D!C2:C21)

The result from this formula is a number much higher than the filter 3D formula since there is no way to use the native 3D referencing to construct a filtered result.

Other types of aggregation from the filtered 3D formulas, but they must be constructed as array formulas. The following formulas show the filtered MIN for the desired range and the corresponding Excel-centric 3D formula.

=MIN(SUBTOTAL(5,INDIRECT(“‘”&CHAR(ROW($97:$100))&”‘!C2:C21”))) ‘array formula

=MIN(A:D!C2:C21)

XL3DST3

This example shows the formulas for the MAX 3D-filtered and Excel 3D.

=MAX(SUBTOTAL(4,INDIRECT(“‘”&CHAR(ROW($97:$100))&”‘!C2:C21”)) ‘array formula

=MAX(A:D!C2:C21)

XL3DST4

There are obviously a number of variations on this theme possible. Knowing that this type of aggregation is available could possibly change the way you analyze your data. HTH!

You can download the file here.

Excel3DSubtotal

 

Using #Excel VBA to Create a Filter Criteria User Defined Function by David Hager

Some of the very best Excel work in the early years was done by Stephen Bullen. His legacy Excel site still exists, but it has not been updated in many years.

http://www.oaltd.co.uk/Excel/Default.htm

One of Stephen’s creations was a “simple” VBA function to return the applied criteria of a filtered list to a worksheet cell (shown below).

 

Function FilterCriteria(Rng As Range) As String

‘By Stephen Bullen

Dim Filter As String

Filter = “”

On Error GoTo Finish

With Rng.Parent.AutoFilter

If Intersect(Rng, .Range) Is Nothing Then GoTo Finish

With .Filters(Rng.Column – .Range.Column + 1)

If Not .On Then GoTo Finish

Filter = .Criteria1

Select Case .Operator

Case xlAnd

Filter = Filter & ” AND ” & .Criteria2

Case xlOr

Filter = Filter & ” OR ” & .Criteria2

End Select

End With

End With

Finish:

FilterCriteria = Filter

End Function

The use of this function is illustrated in the following figure. The formulas are in row 1.

fcfirst

Here is another view with criteria applied to other columns.

fcmid

It also shows a limitation that this function had. Since the .Criteria1 and Criteria2 properties are strings, when criteria is set for a date column, the string contains Excel’s “date number”, not the date formatted number displayed in the cells. In order to workaround this limitation, I amended the function as shown below.

Function FilterCriteriaEnh(Rng As Range) As String ‘Enhanced to handle date filters

‘By Stephen Bullen and David Hager

Dim Filter As String

Dim Criteria2 As String

Filter = “”

sFormat = Application.Index(Rng, 2).NumberFormat

‘On Error GoTo Finish

With Rng.Parent.AutoFilter

If Intersect(Rng, .Range) Is Nothing Then GoTo Finish

With .Filters(Rng.Column – .Range.Column + 1)

If Not .On Then GoTo Finish

Filter = .Criteria1

If sFormat = “m/d/yyyy” Then

Filter = Left(Filter, InStr(Filter, OnlyDigits(Filter)) – 1) & _

Format(OnlyDigits(Filter), sFormat)

On Error GoTo Finish

Criteria2 = Left(.Criteria2, InStr(.Criteria2, OnlyDigits(.Criteria2)) – 1) & _

Format(OnlyDigits(.Criteria2), sFormat)

End If

Select Case .Operator

Case xlAnd

Filter = Filter & ” AND ” & Criteria2

Case xlOr

Filter = Filter & ” OR ” & Criteria2

End Select

End With

End With

Finish:

FilterCriteriaEnh = Filter

End Function

 

Function OnlyDigits(s As String) As String

With CreateObject(“vbscript.regexp”)

.Pattern = “\D”

.Global = True

OnlyDigits = .Replace(s, “”)

End With

End Function

 

First, I needed to capture the format from the column is question to see if it was date formatted.

sFormat = Application.Index(Rng, 2).NumberFormat

If sFormat = “m/d/yyyy” Then

Filter = Left(Filter, InStr(Filter, OnlyDigits(Filter)) – 1) & _

Format(OnlyDigits(Filter), sFormat)

The change in the string for the Filter variable is made by the formula shown above. The OnlyDigits function used in the formula construction is not original, but I do not know the source. It puts the string back together with the date replacing the date system number.

I did the same thing for Criteria2, but it will not exist if a second criteria is not selected in the filter, so I had to add error handling for that scenario.

On Error GoTo Finish

Criteria2 = Left(.Criteria2, InStr(.Criteria2, OnlyDigits(.Criteria2)) – 1) & _

Format(OnlyDigits(.Criteria2), sFormat)

A final filter list example using this enhanced function is shown below.

fclast

You can download the file for this here.

FilterCriteria

#Excel Filtered List Using a Calculated Column to Count Occurrence Number by David Hager

In this example, each record of an industrial process includes a composition (or run) number, a process type and a final status. The goal is to create a column that will show the order of records with filter criteria based on filter settings. The original table before filtering looks like this.

 OccurTable1

The array formula in the Occurrence# column is:

=SUM(N(IF(SUBTOTAL(3,OFFSET($C$2:C2,ROW($C$2:C2)-MIN(ROW($C$2:C2)),,1)),$C$2:C2,””)=C2))

Without going into a lot of detail on how this formula works, based on the selected Composition Number in column C, is uses an auto-expanding range that only produces a value of 1 if the rows are visible and then sums those instances. In this example, selecting only the compositional number 1127 produces the filtered table shown in the figure.

 Filter1

In the Process column, if A and B are removed through filtering, the result is:

 Filter2

 

Finally, if Approved is removed the Status column through filtering, the result is:

 Filter3

It is important to note that this formula is quite calculation-intensive. There are 5000 records in the example workbook, and it takes several seconds to recalculate after each filter change.

You can download the workbook here.

OccurenceNumber