Add +1 if cell above is different

Máté Kiss 0
Viewed 18

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.

excel-formula vba excel
2 Answers

Can try-

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

enter image description here

Harun24hr0

Although Harun's answer is correct, hereby an answer which is more in line with the way you ask your question. The whole idea is based on the formula =IF(A3=A2,B2,B2+1), hereby a screenshot:

enter image description here

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 :-) ).

Dominique0
Related