Help with filling Sr. Nos. based on some other criteria

[sudeep]

I have an excel file with 1st column having Sr. Nos.

Sr. No. Group Name
1 A Anand
1 C Bismillah
2 A Chintan
1 B D'Souza
2 B England
3 C Foolandevi
3 B Gavaskar
4 C Hindmata
4 B Icecream
3 A Iceland
4 A Jamunadas

Note how Serial Nos. are given. This is to accomplish getting correct Sr. Nos when filter is applied on a group. For example: If a filter is applied on group A the result is:

Sr. No. Group Name
1 A Anand
2 A Chintan
3 A Iceland
4 A Jamunadas

Presently, I am doing this manually and takes a long time and I also make errors.

Help is requested for doing this automatically, preferably by entering a formula in the cells instead of a macro.

 

[SydneyGeek]

In A2: =COUNTIF($B$2:B2,B2)

Fill down

Denis