Formula to Count a value and create Sequence number stops and repeats

Gaja Rm

In an source sheet we have list of countries. If India is twice and in destination sheet it is 6 times but the value I need it in sequence i.e., 1,2 and starts again from 1,2 and again 1,2. US is at 3 times in souce hence in destination sheet 1,2,3 and repeats. 

Paul Newcome

You are going to need an auto-number column with no special formatting. Then you can use:

=IF(MOD(COUNTIFS([Country 2]:[Country 2], @cell = [Country 2]@row, Auto:Auto, @cell <= Auto@row), COUNTIFS(Country:Country, @cell = [Country 2]@row)) = 0, COUNTIFS(Country:Country, @cell = [Country 2]@row), MOD(COUNTIFS([Country 2]:[Country 2], @cell = [Country 2]@row, Auto:Auto, @cell <= Auto@row), COUNTIFS(Country:Country, @cell = [Country 2]@row)))

Gaja Rm

@Paul Newcome

Paul Newcome

You are going to need an auto-number column with no special formatting. Then you can use:

=IF(MOD(COUNTIFS([Country 2]:[Country 2], @cell = [Country 2]@row, Auto:Auto, @cell <= Auto@row), COUNTIFS(Country:Country, @cell = [Country 2]@row)) = 0, COUNTIFS(Country:Country, @cell = [Country 2]@row), MOD(COUNTIFS([Country 2]:[Country 2], @cell = [Country 2]@row, Auto:Auto, @cell <= Auto@row), COUNTIFS(Country:Country, @cell = [Country 2]@row)))

Gaja Rm

@Paul Newcome Perfect Thanks lot.

Paul Newcome

Happy to help. 👍️