Monthly Archives: March 2017

#Excel: Creating A List Made Up Of Formulas From A Filtered List by David Hager

Well, after working on the previous post about occurrence number in a filtered list,

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

I started to think about how to return the row number of the last row in a filtered list. I found quite a few examples on the net about how to do this with VBA, but I could not find anywhere someone had accomplished this with an Excel array formula. With a slight modification of the formula in that post, I came up with this.

=MAX(IF(SUBTOTAL(3,OFFSET($C$2:C5001,ROW($C$2:C5001)-MIN(ROW($C$2:C5001)),,1)),ROW(1:5000),0))+1

And, it worked! But, then I realized that this formula had a much greater potential. If the MAX aggregation was replaced by the SMALL function, it could return ALL of the row positions in the filtered list. So, I moved the list down and created my formulas to return all of the filtered records. The result is shown below.

FFT1

As you can see, all of the filtered records in the top list created by formulas are identical to those in the filtered list. The key formula in cell A2 is:

=INDIRECT(“a”&SMALL(IF(SUBTOTAL(3,OFFSET($C$24:C5023,ROW($C$24:C5023)-MIN(ROW($C$24:C5023)),,1)),ROW(1:5000),””),ROW()-1)+25-ROW())

If “a” is substituted by “b”, “c”, “d” and “e” in the same formula filled to B2:E2, those five formulas can then be filled down to create the formula list. I have been working with filtered lists for a long time, and I always used a VBA procedure to copy/paste that information to another location. Now, this “static” list makes it very easy to use filtered results. I hope that you find this technique useful in your work.

The workbook can be downloaded here.

FilteredResultsTable

 

#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

 

 

Using the #Excel Advanced Filter to a List Having Internal Numbers in a String By David Hager

The Excel advanced filter is likely the most underused of all Excel features relative to its power to analyze data. Great examples of the use of the advanced filter feature have been published by Tushar Mehta, Debra Dalgleish and Charley Kyd respectively, who not coincidentally have all been Microsoft Excel MVPs.

http://www.tushar-mehta.com/publish_train/data_analysis/06.shtml

http://www.contextures.com/xladvfilter02.html

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

Update:

There is another reference I wanted to include in the post, but just found. It is:

http://www.get-digital-help.com/2016/12/20/filter-rows-where-a-cell-contains-a-numeric-value/

I could not find an example in my Internet search of a way to filter based on strings with internal numbers in a column list. So, I decided to fill that void. There can be multiple criteria in an advanced filter, in this case only is needed. Given that A5 is the first item in the list, the following formula can be used.

=OR(NOT(ISERROR(FIND({0,1,2,3,4,5,6,7,8,9},A5))))

The figure below shows how the advanced filter was set up.

 afn1

This is based on the assumption that all of the strings in the list would contain internal numeric strings or nothing at all. The result is:

 afn2

If necessary, the following formula will filter all strings containing no numbers and those where the first or last character are numbers.

=AND(NOT(ISNUMBER(VALUE(LEFT(A5,1)))),NOT(ISNUMBER(VALUE(RIGHT(A5,1)))),OR(NOT(ISERROR(FIND({0,1,2,3,4,5,6,7,8,9},A5)))))

The result is:

afn3

Since this example assumes only 1 internal string of numbers in each string, it cannot handle (filter out) a case such as ae326tw207def. Perhaps I will tackle that scenario in a future post. Enjoy!

You can download the file here.

AdvFiltNumbers

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.

 unFig1

After filtering (removing the letters b,e,f,g), the resulting filtered list looks like this.

unFig2 

I hope that this is another useful tool to add to your Excel bag of tricks. You can download the file from the link below. Enjoy!

unFiltered

Chapter on Conditional Formatting in Excel by David Hager (a Blast from the Past)

I wrote this conditional formatting article almost 20 years ago, with the hope that it would  be included in a book. Unfortunately, it never made it. I then published it as part of my Excel E-Experts series. The text files were recently added to this blog.

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

This article was perhaps one of my best works in Excel. So, although it was written long ago, it still contains valuable information that I am sharing here. The following file links are the working Excel file and the article document. Enjoy!

CONDFORMAT1

CFarticle

 

 

My Collection of Excel Tips by David Hager

“#Excel, did you know?” that you can use a CUBE function in an #Excel conditional formatting formula.
“#Excel 2013 did you know” that there are download files in Help for new worksheet functions.
“#Excel, did you know?” that chart based on TABLE will auto-expand when new data is added.
“#Excel, did you know” that you drag/drop data from one #Excel window to another?
“#Excel did you know?” you can use a defined name range on an xl4 macro sheet in a formula?
“#Excel did you know?” that you can compare two columns by using GoTo Special – Row Differences?
“#Excel did you know?” that if a number of worksheets are selected, right-clicking one will make it the active sheet.
“#Excel, did you know?” magic UDF part 2: .AddComment .Comment.Text CStr(Now()) End With End Function
“#Excel, did you know?” magic UDF part 1: Function TS() Application.Volatile True On Error Resume Next TS = “TS” With Application.Caller
“#Excel, did you know?” you can make and store your own custom cell styles.
“#Excel, did you know?” if you fill a table across worksheets, new tables are made automatically in the destination worksheets.
“#Excel, did you know?” you can import XML from Internet which becomes a data table that you can then load into PowerPivot.
“#Excel, did you know?” that you can use Insert, Object to place a linked Excel file inside itself.
“#Excel, did you know?” there are >250 commands that are not accessible through the ribbon, but can be added to the Quick Access Toolbar.
“#Excel, did you know?” you can r-cl the cell below a text list & select “Pick from drop-down list” to pick an entry in the list.
“#Excel, did you know?” you can right-click the scroll buttons in lower left corner to access a list of all worksheets in workbook.
“#Excel, did you know?” you can create a defined name formula (DNF) from an UDF without arguments.The DNF can then be used in other formulas.
“#Excel, did you know?” you can write an external link formula in a cell that points to a cell in an #Excel file stored on the Internet.
“#Excel, did you know?” you can return the sum of top 3 values of a filtered column with =SUM(AGGREGATE(14,1,filtered_col,ROW(1:3))) CSE
“#Excel, did you know?” r-cl a shape, cl 1st option & enter VBA procname in Address box preceded by #. cl on shape to go to that proc in VBE.
“#Excel, did you know?” you can link a data range in an external workbook to a sparkline.
“#Excel, did you know?” you can link a cell in an external workbook to a shape.
“#Excel, did you know?” Convert formulas to values: Select range, right-click edge, pull away, drop in original area and select menu item.
“#Excel, did you know?” that sparklines can use defined name formulas such as =OFFSET($A$1,1,$G$1,COUNTA($A:$A)-1,1) as their input range.
“#Excel, did you know?” that sparklines autoadjust with auto-expansion of tables if associated with a vertical range.
“#Excel, did you know?” that sparklines respond to filters in tables if associated with a vertical range.

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

critunfilt

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

critlarge

SmallCondition=OR(Sheet1!B6<=AGGREGATE(15,1,Sheet1!$B$6:$B$31,ROW(INDIRECT(“$1:$”&TopN))))

critsmall

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.

critboth

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