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

 

 

#Excel Blog Posts Having Downloadable Workbook Files by David Hager

Here is a list of blog posts (UPDATED – 04/14/2017) on this site which have attached files that can be downloaded.

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

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

#Excel VBA: Create a Table of File Locations and URLs for Your Favorites by David Hager

https://dhexcel1.wordpress.com/2017/04/11/excel-vba-create-a-table-of-file-locations-and-urls-for-your-favorites-by-david-hager/

#Excel Identifying if a String is the Anagram of Another String Using the TEXTJOIN Function by David Hager

https://dhexcel1.wordpress.com/2017/04/10/excel-identifying-if-a-string-is-the-anagram-of-another-string-using-the-textjoin-function-by-david-hager/

Mine3D for #Excel: An Excel-based Game by David Hager

https://dhexcel1.wordpress.com/2017/04/08/mine3d-for-excel-an-excel-based-game-by-david-hager/

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

https://dhexcel1.wordpress.com/2017/04/07/using-the-subtotal-function-in-excel-to-aggregate-values-from-multiple-filtered-lists-by-david-hager/

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

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

Multiple Lookup Values in a Single Cell (With/Without Duplicates) Using Only #Excel Formulas by David Hager

https://dhexcel1.wordpress.com/2017/04/04/multiple-lookup-values-in-a-single-cell-withwithout-duplicates-using-only-excel-formulas-by-david-hager/

#Excel Shift Calendar by David Hager

https://dhexcel1.wordpress.com/2017/04/03/excel-shift-calendar-by-david-hager/

Generating a Sorted Unique Array in #Excel using Only Formulas by David Hager

https://dhexcel1.wordpress.com/2017/04/01/generating-a-sorted-unique-array-in-excel-using-only-formulas-by-david-hager/

#Excel: Creating A List Made Up Of Formulas From A Filtered List 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/

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

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

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

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

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

https://dhexcel1.wordpress.com/2017/03/23/using-conditional-formatting-to-highlight-unique-items-in-an-excel-filtered-list-by-david-hager/

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

https://dhexcel1.wordpress.com/2017/03/13/chapter-on-conditional-formatting-in-excel-by-david-hager-a-blast-from-the-past/

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

https://dhexcel1.wordpress.com/2017/03/02/using-the-choose-and-aggregate-functions-to-apply-conditional-formatting-to-a-filtered-list-in-excel-by-david-hager/

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

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/

Archive of Excel Experts E-Letter (by David Hager)

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

Creating an Excel Table of Components By Product From a List Using the TEXTJOIN Function By David Hager

https://dhexcel1.wordpress.com/2017/01/07/creating-an-excel-table-of-components-by-product-from-a-list-using-the-textjoin-function-by-david-hager/

Using the Excel TEXTJOIN Function To Return Unique Items In A One-Cell Delimited String From A 2D and 3D Range By David Hager

https://dhexcel1.wordpress.com/2017/01/02/using-the-excel-textjoin-function-to-return-unique-items-in-a-one-cell-delimited-string-from-a-2d-and-3d-range-by-david-hager/

xlCube: An Excel game

https://dhexcel1.wordpress.com/2016/11/29/xlcube-an-excel-game/

Power BI Help for Excel

https://dhexcel1.wordpress.com/2015/08/31/power-bi-help-for-excel/

BINGO! Excel Power Formula

https://dhexcel1.wordpress.com/2015/03/31/bingo/

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.