PDA

View Full Version : Solved: Occurance Count



khalid79m
02-04-2010, 09:16 AM
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
02-04-2010, 10:09 AM
Use this formula, and copy down.

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

khalid79m
02-04-2010, 10:17 AM
xld your the man , i did it with the code below but yours is so simplier i will use thanks


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

khalid79m
02-04-2010, 11:06 AM
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
02-04-2010, 12:18 PM
This should do it

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

khalid79m
02-04-2010, 03:32 PM
Your good ... thanks