Reset cell value to 0 and start count again

[Kamesh63]

I have a column of 10000 cells (say H1:H10000), which use countif formula to generate numbers from 1 upwards based on matching criteria in the range (say G1:G10000). Now I want the values in H1:H10000 to be reset to 0 whenever it reaches 50 and then start counting again (like a counter). Can I do this using a formula?

For example G1 to G10000 have values "AA"
The formula used in H1 is Countif($G$1:G1,G1) copied down till H10000

If G1 to G 50 contains "AA" then H1to H50 shows incremental values from 1 to 50. Now from H51 I want it to be reset to 1 and start counting till next 50 and so on till all matching criteria are completed. For every 50 values the counter should be reset to 1.
Any help is deeply appreciated.

 

[bosco_yip]

Try,

In H1, copy down :

=MOD(COUNTIF($G$1:G1,G1)-1,50)+1

Regards
Bosco

 

[Kamesh63]

Thanks Bosco. Works very well indeed. Have gone through concept of modulus from maths book and understood how this works. (Being a science student didn't have much maths exposure)
Thanks again!