#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

 

Advertisements

3 thoughts on “#Excel: Creating A List Made Up Of Formulas From A Filtered List by David Hager

  1. Pingback: #Excel Blog Posts Having Downloadable Workbook Files by David Hager | Excel For You

  2. Pingback: #Excel: Combining and Refining a Static Filter List & Filter Criteria UDF by David Hager | Excel For You

  3. Pingback: #Excel Super Links #90 (Special Edition) – shared by David Hager | Excel For You

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s