Solved: Occurance Count

[khalid79m]

I have a list of Keys in column B

Column B2 to say B500 (B500 this could vary(lastrow))
has keys in similar to the ones listed below


South_00123
South_00123
South_00154
South_00154
South_00154
South_00154
South_00154
South_00154
South_00159
South_00159
South_00159


I want to count the number of occurance in order

so
columnb ColumnC
South_00123 1
South_00123 2
South_00154 1
South_00154 2
South_00154 3
South_00154 4
South_00154 5
South_00154 6
South_00159 1
South_00159 2
South_00159 3

Can anyone help

[Bob Phillips]

Use this formula, and copy down.

=COUNTIF($B$2:B2,B2)

[khalid79m]

xld your the man , i did it with the code below but yours is so simplier i will use thanks


[VBA]Sub Test1()
Dim Z As Integer
Dim x As Integer

NumRows = Range("K2", Range("K2").End(xlDown)).Rows.Count
Range("K2").Select
For x = 1 To NumRows

If ActiveCell.Value = ActiveCell.Offset(-1, 0) Then

ActiveCell.Offset(0, 1).Value = Z
Z = Z + 1
Else

ActiveCell.Offset(0, 1).Value = 1
Z = 2

End If

ActiveCell.Offset(1, 0).Select

Next

End Sub[/VBA]

[khalid79m]

Slight problem XLD

I need it work across 2 rows of data

columnb ColumnC Columnd
South_00123 xx 1
South_00123 xx 2
South_00154 aa 1
South_00154 aa 2
South_00154 aa 3
South_00154 xx 1
South_00154 xx 2
South_00154 cc 1
South_00159 aa 1
South_00159 aa 2
South_00159 cc 1

[Bob Phillips]

This should do it

=SUMPRODUCT(--($A$2:$A2=$A2),--($B$2:$B2=$B2))

[khalid79m]

Your good ... thanks