# #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. 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/04/06/using-excel-vba-to-create-a-filter-criteria-function-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. 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.

# #Excel: Using Advanced Filter with a Custom List 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: The advanced filter information is set up as shown below. Finally, the list after filtering looks like this: 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.

# 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).

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.  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) 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) 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!

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. Here is another view with criteria applied to other columns. 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. 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. 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. In the Process column, if A and B are removed through filtering, the result is: Finally, if Approved is removed the Status column through filtering, the result is: 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.

OccurenceNumber

# Using Conditional Formatting to Highlight Unique Items in an Excel Filtered List By David Hager

Quite a while back I created a formula to count the number of unique items in a filtered list. For examples, see:

http://blog.contextures.com/archives/2010/10/04/count-unique-items-in-excel-filtered-list/

and

https://dhexcel1.wordpress.com/2017/01/08/creating-a-unique-delimited-string-from-an-excel-filtered-list-by-using-the-textjoin-function-by-david-hager/

I decided to extend this methodology to the conditional formatting of a filtered list. The following defined name formulas are required.

Rge=\$A\$5:\$A\$29

unRge=IF(SUBTOTAL(3,OFFSET(Rge,ROW(Rge)-MIN(ROW(Rge)),,1)),Rge,””)

cfUnRge=INDEX((N(IF(ISNA(MATCH(“”,unRge,0)),MATCH(Rge,Rge,0),IF(MATCH(unRge,unRge,0)=MATCH(“”,unRge,0),0,MATCH(unRge,unRge,0)))=ROW(Rge)-MIN(ROW(Rge))+1)),ROW()-4)

(The cursor must be on A5 when the cf function is defined and applied to A5:A29)

So, before filtering, the list shows the 1st unique items highlighted in yellow. After filtering (removing the letters b,e,f,g), the resulting filtered list looks like this. unFiltered

# Using the CHOOSE and AGGREGATE Functions To Apply Conditional Formatting to a Filtered List in Excel By David Hager

The very 1st article I wrote for my blog was about highlighting values in a filtered list. I decided that it was time to revisit that subject. Specifically, I had written an article for Rob Collie about using the CHOOSE function with conditional formatting (CF) in Excel. It turned out that the article was too complex to be useful.

https://dhexcel1.wordpress.com/2013/12/19/highlighting-top-n-values-in-a-filtered-table-column-using-conditional-formatting/

https://powerpivotpro.com/2012/11/david-hager-on-dynamic-conditional-formatting/

So, I decided to incorporate the use of the CHOOSE function into my original article and give it a friendly user interface. The two user inputs were both created using Data Validation with the List option. They are defined as shown below and pictured in the following figure (showing unfiltered list). TopN=Sheet1!\$B\$1

Crit=Sheet1!\$B\$2

These inputs are used to return values needed by the CF conditions. TopN is selected by the user in cell B1. Cell B2 gives a choice of Large or Small. The following formula converts this information into a number to be used by the CHOOSE function.

CritNumber=IF(Crit=”Large”,1,2)

The CF conditions are made to be applied to column B. When each formula is defined, the active cell needs to be B6.

LargeCondition=OR(Sheet1!B6>=AGGREGATE(14,1,Sheet1!\$B\$6:\$B\$31,ROW(INDIRECT(“\$1:\$”&TopN)))) SmallCondition=OR(Sheet1!B6<=AGGREGATE(15,1,Sheet1!\$B\$6:\$B\$31,ROW(INDIRECT(“\$1:\$”&TopN)))) When combined with the CHOOSE function (the formula used for the CF), they provide a methodology to highlight filtered rows, as shown below (filter set to display values of 50 to 175 in column B).

=CHOOSE(CritNumber,LargeCondition,SmallCondition)

If a way to visualize TopN and BottomN simultaneously was required, a second CF condition can be appled to column B.

=CHOOSE(CritNumber, SmallCondition, LargeCondition)

The following figure shows the result. This is just a demonstration of the versatility of using CF condtions in filtered lists. The application of this method to many columns can be done, as well as the use of different CF conditions. I might write another article about this subject later, but for now, enjoy! Click the link below to download the working file.

aggregate_filter_choose

# Creating a Unique Delimited String from an Excel Filtered List by Using the TEXTJOIN Function By David Hager

Some time ago (16 years – which is 100 in Excel years) I developed a formula solution for counting the number of unique items in a filtered list. I realized that this methodology could be used with the new TEXTJOIN function in Excel (Office 365 version). See the original publication in EEE# 20:

https://dhexcel1.wordpress.com/2017/01/07/archive-of-excel-experts-e-letter-by-david-hager/

The following defined names are needed to construct the formula.

Rge=Sheet1!\$A\$2:\$A\$20

unRge=IF(SUBTOTAL(3,OFFSET(Rge,ROW(Rge)-MIN(ROW(Rge)),,1)),Rge,””)

unRge returns an array that contains only filtered items. In the figure, rows 2,3,4,6,8,10,11,12,15,17,18,19,and 20 are visible. To return a unique delimited string for only those visible rows, use the following formula (in E3):

=TEXTJOIN(“,”,TRUE,IF(N(IF(ISNA(MATCH(“”,unRge,0)),MATCH(Rge,Rge,0),IF(MATCH(unRge,unRge,0)=MATCH(“”,unRge,0),0,MATCH(unRge,unRge,0)))=ROW(Rge)-MIN(ROW(Rge))+1),unRge,””))

which affords

a,c,h,l,v,m,d,w,g,o,t