Excel Formula help

[DaveH23]

I have a sheet of data that I need to create a sequence counter based on the result of another column.

If Column A has numbers sorted numerically but repeats itself I want the column alongside it start counting and reset and the change in column A i.e:

A	B
1	1
1	2
1	3
1	4
2	1
2	2
2	3
3	1
3	2
4	1
4	2
4	3
4	4

Is this easily done?

[RizzoTheRat]

or =COUNTIF($A$1:Axx,Axx) where xx is the row number, which will cope with column A not being sorted in order

[DaveH23]

That's not working unfortunately.

I can half understand how its reading but the ,1 at the end is throwing me.

[DaveH23]

RizzoTheRat said:

or =COUNTIF($A$1:Axx,Axx) where xx is the row number, which will cope with column A not being sorted in order

Perfect, This place never ceases to amaze me at times.

Thank you.

[RizzoTheRat]

DaveH23 said:

Jasey_ said:

=IF(A2=A3, B2+1,1)

That's not working unfortunately.

I can half understand how its reading but the ,1 at the end is throwing me.

That's the formula you'd need in B3, and copy down. It's saying if the value in A is the same as the line above, set B to the value in B of the row above plus 1, otherwise put a 1 in B because the A values aren't the same so it's the first instance of that A value.

If there's a lot of data I think this one will calculate faster than the COUNTIF approach as COUNTIF has to a bigger check with each successive line, but unless you have thousands of lines it's not likely to be an issue.