I am trying to find a formula or VBA code for the following scenario:

I have a set of data where numbers in column B appear like this:

2000228
2000228
2000228
2000228
2000230
2000230
2000230
2000230
2000232
2000232
2000232

I would like to have column A populated with "1" for the first 4 rows and then add 1 each time the numbers are changing, meaning it would "2" for the next 4 and "3" for the next 3 and so on.

I know there is a formula or VBA for this i found it before but cant think of it now how i search for it.

CodePudding user response：

Can try-

=COUNTA(UNIQUE($A$1:$A1))

CodePudding user response：

Although

The formula means: if the left cell equals the left above cell, just copy the value above, otherwise add 1 (which is in fact what you asked for :-) ).