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

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.

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.

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

#Excel For You Blog Posts Having Downloadable Workbook Files by David Hager

Here is a list of Excel For You blog posts (UPDATED – 06/07/2017) which have attached files.

Using Excel UDF to Translate a Phrase From One Language to Another by David Hager

https://dhexcel1.wordpress.com/2017/06/03/creating-an-excel-translator-by-david-hager/

Using #Excel to Make A Round Robin Tournament Schedule by David Hager

https://dhexcel1.wordpress.com/2017/06/02/using-excel-to-make-a-round-robin-tournament-schedule-by-david-hager/

#Excel Short and Sweet Tip #19 (Invaluable Excel Speller) by David Hager

https://dhexcel1.wordpress.com/2017/05/28/excel-short-and-sweet-tip-19-excel-can-spell-by-david-hager/

#Excel Short and Sweet Tip #18 (Toggle Formatting on Worksheet) by David Hager

https://dhexcel1.wordpress.com/2017/05/27/excel-short-and-sweet-tip-18-toggle-formatting-on-worksheet-by-david-hager/

#Excel: Project Tracking Workbook by David Hager

https://dhexcel1.wordpress.com/2017/05/25/excel-project-tracking-workbook-by-david-hager/

#Excel Short & Sweet Tip #16 (Multiple Delimiters with TEXTJOIN for Custom Formatting) by David Hager

https://dhexcel1.wordpress.com/2017/05/24/excel-short-sweet-tip-16-multiple-delimiters-with-textjoin-for-custom-formatting-by-david-hager/

#Excel Native 3D Ranges with the TEXTJOIN Function Plus Bonus by David Hager

https://dhexcel1.wordpress.com/2017/05/23/excel-native-3d-ranges-with-the-textjoin-function-plus-bonus-by-david-hager/

#Excel UDF Using Google API to Return the Elevation of an Address by David Hager

#Excel: Creating a List of Option Expiration Dates and Triple Witching Dates with Excel Formulas by David Hagerthat can be downloaded.

https://dhexcel1.wordpress.com/2017/05/22/excel-creating-a-list-of-option-expiration-dates-and-triple-witching-dates-with-excel-formulas-by-david-hager/

Copy #Excel Chart as a Enhanced Metafile Picture by David Hager

https://dhexcel1.wordpress.com/2017/05/20/copy-excel-chart-as-a-enhanced-metafile-picture-by-david-hager/

#Excel: Removing Outliers with Excel Formulas to Modify Control Limits by David Hager

https://dhexcel1.wordpress.com/2017/05/17/excel-modifying-control-chart-data-to-remove-outliers-with-excel-formulas-by-david-hager/

#Excel: Origin of Sparklines – LineChart VBA User-Defined Function by David Hager

https://dhexcel1.wordpress.com/2017/05/15/excel-origin-of-sparklines-linechart-vba-user-defined-function-by-david-hager/

#Excel: Finding and Visualizing the Last Record in a Table Based on Criteria by David Hager

https://dhexcel1.wordpress.com/2017/05/14/excel-finding-and-visualizing-the-last-record-in-a-table-based-on-criteria-by-david-hager/

#Excel Short and Sweet Tip #12 (Documenting Formulas and Highlighting Those Formulas With Conditional Formatting) by David Hager

https://dhexcel1.wordpress.com/2017/05/13/excel-short-and-sweet-tip-12-documenting-formulas-and-highlighting-those-formulas-with-conditional-formatting-by-david-hager/

#Excel: Creating a Environmental Variables Table with the VBA ENVIRON Function UDF by David Hager

https://dhexcel1.wordpress.com/2017/05/12/excel-creating-a-udf-with-the-vba-environ-function-and-using-it-to-make-a-table-of-environmental-variables-by-david-hager/

#Excel Gems: A Valuable Collection of Excel and Power BI Links to Great Excel and Modern Excel Techniques by David Hager

#Excel: Building a Frequency Summary Table Based on an Excel List by David Hager

https://dhexcel1.wordpress.com/2017/05/10/excel-building-a-frequency-summary-table-based-on-an-excel-list-by-david-hager/

#Excel: A Model Using the CONVERT Function Containing Categories by David Hager

https://dhexcel1.wordpress.com/2017/05/08/excel-a-model-using-the-convert-function-containing-categories-by-david-hager/

#Excel Magic Consolidator by David Hager

https://dhexcel1.wordpress.com/2017/05/07/excel-magic-consolidator-by-david-hager/

#Excel: Using Conditional Formatting to Highlight Cells That Contain Array Formulas Using the FORMULATEXT Function by David Hager

https://dhexcel1.wordpress.com/2017/05/05/excel-using-conditional-formatting-to-highlight-cells-that-contain-array-formulas-using-the-formulatext-function-by-david-hager/

#Excel: Generating a Random Sampling From a List Using VBA and the TEXTJOIN Function by David Hager

https://dhexcel1.wordpress.com/2017/05/03/excel-generating-a-random-sampling-from-a-list-using-vba-and-the-textjoin-function-by-david-hager/

#Excel Worksheet UDF that Adds a Comment to Any Cell by David Hager

#Excel: Using Conditional Formatting to Highlight 3D Formulas with Defined Names by David Hager

https://dhexcel1.wordpress.com/2017/04/25/excel-using-conditional-formatting-to-highlight-3d-formulas-with-defined-names-by-david-hager/

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

https://dhexcel1.wordpress.com/2017/04/24/excel-using-conditional-formatting-to-highlight-cells-containing-native-3d-formulas-by-david-hager/

#Excel: Using Conditional Formatting to Highlight Cells Containing User-Defined Functions by David Hager

https://dhexcel1.wordpress.com/2017/04/22/excel-using-conditional-formatting-to-highlight-cells-containing-user-defined-functions-by-david-hager/

#Excel: Modifying Shapes From An UDF in a Worksheet Cell by David Hager

https://dhexcel1.wordpress.com/2017/04/19/excel-modifying-shapes-from-an-udf-in-a-worksheet-cell-by-david-hager/

#Excel: Combining and Refining a Static Filter List & Filter Criteria UDF by David Hager

https://dhexcel1.wordpress.com/2017/04/15/excel-combining-and-refining-a-static-filter-list-filter-criteria-udf-by-david-hager/

#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

#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

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

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:

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:

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!

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

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.