Adding a sequence to filtered data

[knowthediff]

I have the following data:
Bob
Mary
Bean
Scott

I want it to have a sequence when printed when it is filtered or not.
So, if I printed it all I would like to see:

1 Bob
2 Mary
3 Bean
4 Scott

If I filtered on Column B for the items that start with the letter "B"
I would like to see (notice the sequence is accurately reflecting the
number of rows displayed):
1 Bob
2 Bean

Is this possible with simple formulas? I know I can write a macro
that will populate the values each time it is run but I would prefer
to use formulas.

Regards,
-J

 

[BoniM]

If you want the count in column a and your names are in column b, beginning
with row 2, enter the following formula in A2 and copy down the length of
your data. The numbers will be properly sequenced when filtered.

=SUBTOTAL(103,$B$2:B2)

 

[knowthediff]

Works like a charm, Thanks!

-J

If you want the count in column a and your names are in column b, beginning
with row 2, enter the following formula in A2 and copy down the length of
yourdata. The numbers will be properly sequenced whenfiltered.

=SUBTOTAL(103,$B$2:B2)










- Show quoted text -