Sequential numbering based on change in another column

[heathball]

HELLO, I'M TRYING TO GET A FUNCTION FOR A NEW COLUMN (COL 'DP') TO ACHIEVE THE RESULT BELOW.
i CANNOT FIND A WAY TO DO THIS. i AM TRYING 'COUNTIFS' BUT ITS NOT WORKING.

i HAVE A LARGE FILE, AND EACH TIME THERE IS A CHANGE IN COL A, I NEED THE NUMBERS TO START AGAIN FROM '1' IN A NEW COLUMN.

I HOPE SOMEONE CAN HELP, THANKS




COL A COL DP
PETER 1
PETER 2
PETER 3
PETER 4
JOHN 1
JOHN 2
JOHN 3
JOHN 4
JOHN 5
JOHN 6
ALEX 1
ALEX 2
ALEX 3
ALEX 4
ALEX 5
STEVE 1
STEVE 2

 

[gaz_chops]

Try

=IF(A2=A1,1+DP1,1)

[COLOR=#000000][FONT=-webkit-standard][TABLE="width: 522"]
<colgroup><col width="87" span="6" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="class: xl63, width: 87"]COL A[/TD]
[TD="width: 87"][/TD]
[TD="width: 87"][/TD]
[TD="width: 87"][/TD]
[TD="width: 87"][/TD]
[TD="class: xl63, width: 87"]COL DP[/TD]
[/TR]
[TR]
[TD="class: xl63"]PETER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63"]PETER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl63"]PETER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl63"]PETER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl63"]JOHN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63"]JOHN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl63"]JOHN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl63"]JOHN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl63"]JOHN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl63"]JOHN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl63"]ALEX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63"]ALEX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl63"]ALEX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl63"]ALEX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl63"]ALEX[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl63"]STEVE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63"]STEVE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]
[/FONT][/COLOR]