count distinct numbers

[rishi]

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 count distinct number of ppl offshore and
onsite...
do i need some formula or some code for this...
pls explain its very imp....

 

[Don Guillett]

IF??? I understand your desires, look in the help index for COUNTIF

 

[rishi]

thanks... but i cant find a way for this.... can u pls elaborate the
method...

 

[Tim Williams]

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

 

[rishi]

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

 

[Don Guillett]

Example

The example may be easier to understand if you copy it to a blank worksheet.

How?

1.. Create a blank workbook or worksheet.
2.. Select the example in the Help topic. Do not select the row or column
headers.
From HELP

Selecting an example from Help

3.. Press CTRL+C.
4.. In the worksheet, select cell A1, and press CTRL+V.
5.. To switch between viewing the results and viewing the formulas that
return the results, press CTRL+` (grave accent), or on the Tools menu, point
to Formula Auditing, and then click Formula Auditing Mode.

1
2
3
4
5
A B
Data Data
apples 32
oranges 54
peaches 75
apples 86
Formula Description (Result)
=COUNTIF(A2:A5,"apples") Number of cells with apples in the
first column above (2)
=COUNTIF(B2:B5,">55") Number of cells with a value greater than
55 in the second column above (2)