Hi
I need to count unique items in a list of indices for example:
13.1
13.2
13.3
14.1
15.1
16.1
16.2
The answer would be 4 as the only count I need is the outer index.
The indices are compiled as strings and are in named ranges on several
sheets. I am looking for something like:

lastorderID = 12.1
For i = 1 to sheets .count-1
res(i) = Application.CountIf(Sheets(i).Range("NOrderID"), "<
CInt(lastorderID)")
Next

or:
res(i) = Application.CountIf(Sheets(i).Range("NOrderID"), "<
Left((lastorderID),2)")

But they do not work. Can someone please help?

T.I.A.

try this:

Function uniquecount(source As Range) As Long
Dim index As Long
Dim count As Long
Dim cell As Long
If source.count 0 Then count = 1
For index = 1 To source.count - 1
If Int(source(index)) < Int(source(index + 1)) Then
count = count + 1
End If
Next
uniquecount = count
End Function


"Geoff" wrote in message
...
Hi
I need to count unique items in a list of indices for example:
13.1
13.2
13.3
14.1
15.1
16.1
16.2
The answer would be 4 as the only count I need is the outer index.
The indices are compiled as strings and are in named ranges on several
sheets. I am looking for something like:

lastorderID = 12.1
For i = 1 to sheets .count-1
res(i) = Application.CountIf(Sheets(i).Range("NOrderID"), "<
CInt(lastorderID)")
Next

or:
res(i) = Application.CountIf(Sheets(i).Range("NOrderID"), "<
Left((lastorderID),2)")

But they do not work. Can someone please help?

T.I.A.

Hi Patrick
If my source is Sheets(3).Range("NOrderID") then

MsgBox uniquecount(Sheets(3).Range("NOrderID")) returns 1
whereas the answer should be 5

I am missing something and would be grateful for any further assistance.

Geoff

"Patrick Molloy" wrote:

try this:

Function uniquecount(source As Range) As Long
Dim index As Long
Dim count As Long
Dim cell As Long
If source.count 0 Then count = 1
For index = 1 To source.count - 1
If Int(source(index)) < Int(source(index + 1)) Then
count = count + 1
End If
Next
uniquecount = count
End Function


"Geoff" wrote in message
...
Hi
I need to count unique items in a list of indices for example:
13.1
13.2
13.3
14.1
15.1
16.1
16.2
The answer would be 4 as the only count I need is the outer index.
The indices are compiled as strings and are in named ranges on several
sheets. I am looking for something like:

lastorderID = 12.1
For i = 1 to sheets .count-1
res(i) = Application.CountIf(Sheets(i).Range("NOrderID"), "<
CInt(lastorderID)")
Next

or:
res(i) = Application.CountIf(Sheets(i).Range("NOrderID"), "<
Left((lastorderID),2)")

But they do not work. Can someone please help?

T.I.A.

Hi Patrick
Further to the last - more accurately the return was counting where there
was a blank. I then set If source.count 0 Then count = 1 to
If source.count 0 Then count = 0 and all was well. All I have to do now
is iterate through the sheets and job done.
Thank you.

Geoff

"Geoff" wrote:

Hi Patrick
If my source is Sheets(3).Range("NOrderID") then

MsgBox uniquecount(Sheets(3).Range("NOrderID")) returns 1
whereas the answer should be 5

I am missing something and would be grateful for any further assistance.

Geoff

"Patrick Molloy" wrote:

try this:

Function uniquecount(source As Range) As Long
Dim index As Long
Dim count As Long
Dim cell As Long
If source.count 0 Then count = 1
For index = 1 To source.count - 1
If Int(source(index)) < Int(source(index + 1)) Then
count = count + 1
End If
Next
uniquecount = count
End Function


"Geoff" wrote in message
...
Hi
I need to count unique items in a list of indices for example:
13.1
13.2
13.3
14.1
15.1
16.1
16.2
The answer would be 4 as the only count I need is the outer index.
The indices are compiled as strings and are in named ranges on several
sheets. I am looking for something like:

lastorderID = 12.1
For i = 1 to sheets .count-1
res(i) = Application.CountIf(Sheets(i).Range("NOrderID"), "<
CInt(lastorderID)")
Next

or:
res(i) = Application.CountIf(Sheets(i).Range("NOrderID"), "<
Left((lastorderID),2)")

But they do not work. Can someone please help?

T.I.A.

"Geoff" wrote...
I need to count unique items in a list of indices for example:
13.1
13.2
13.3
14.1
15.1
16.1
16.2
The answer would be 4 as the only count I need is the outer index.
The indices are compiled as strings and are in named ranges on several
sheets. I am looking for something like:

This could be done in a worksheet cell formula using

=SUMPRODUCT(1/COUNTIF(SomeRange,INT(SomeRange)&"*"))

In VBA, I'd be tempted to cheat and use Evaluate.

"Harlan Grove" wrote...
....
This could be done in a worksheet cell formula using

=SUMPRODUCT(1/COUNTIF(SomeRange,INT(SomeRange)&"*"))
....

Not general enough. Make that

=SUMPRODUCT(1/COUNTIF(SomeRange,INT(SomeRange)&".*"))