hey thanks a lot.... that code got my job done...


On May 11, 10:30 am, "Tim Williams" <timjwilliams at gmail dot com
wrote:
Select your "on/off" column data and then run this.

Tim

Sub Count()

Dim id, c
Dim onD As Object, offD As Object
Set onD = CreateObject("scripting.dictionary")
Set offD = CreateObject("scripting.dictionary")

For Each c In Selection
id = c.Offset(0, 1).Value
If c.Value = "Onsite" Then
If Not onD.Exists(id) Then onD(id) = "y"
ElseIf c.Value = "Offshore" Then
If Not offD.Exists(id) Then offD(id) = "y"
Else
'handle these ?
End If
Next c

MsgBox "On: " & onD.Count & vbCrLf & _
"Off: " & offD.Count

End Sub





i have some data as follows

PLACE id number
Offshore 1
Onsite 10
Onsite 11
Onsite 12
Offshore 2
Onsite 12
Offshore 3
Offshore 3
Onsite 13
Offshore 4
Offshore 5
Onsite 14
Offshore 4
Offshore 6
Offshore 6
Offshore 2
Offshore 7

please tell me how do i countdistinctnumber of ppl offshore and
onsite...
do i need some formula or some code for this...
pls explain its very imp....- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -