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

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.

The workbook can be downloaded here.