I am trying to come up with a function or some way to return the lowest
repeatable number in a range of numbers. Here is a function that I
have. It works but does not look pretty. Does anyone have a better
routine or idea to acomplish this?

Thanks
Scott

Function LowestRepeatableNumber(myRange As Range)
Dim LastCount As Integer
Dim LowestNumber
LastCount = 0
LowestNumber = Application.WorksheetFunction.Max(myRange)
For Each Value In myRange
If Value = 0 Then GoTo 10
b = Application.WorksheetFunction.CountIf(myRange, Value)
If b 2 And Value < LowestNumber Then
LowestNumber = Value
End If
10 Next Value
LowestRepeatableNumber = LowestNumber
End Function

Looks like a reasonable approach to me.

--
Regards,
Tom Ogilvy



"Riddler" wrote:

I am trying to come up with a function or some way to return the lowest
repeatable number in a range of numbers. Here is a function that I
have. It works but does not look pretty. Does anyone have a better
routine or idea to acomplish this?

Thanks
Scott

Function LowestRepeatableNumber(myRange As Range)
Dim LastCount As Integer
Dim LowestNumber
LastCount = 0
LowestNumber = Application.WorksheetFunction.Max(myRange)
For Each Value In myRange
If Value = 0 Then GoTo 10
b = Application.WorksheetFunction.CountIf(myRange, Value)
If b 2 And Value < LowestNumber Then
LowestNumber = Value
End If
10 Next Value
LowestRepeatableNumber = LowestNumber
End Function

The logic is good but the syntax could be cleaned up a little. Be sure to
declare all of your variables and the return value of your function. Try to
avoid Goto's as they can make things very convoluted. Beyond that I would not
use Value as a variable as it is a reserved word in VBA... So something more
like this

Function LowestRepeatableNumber(myRange As Range) As Long
Dim LowestNumber As Long
Dim Cell As Range
Dim Count As Long

LowestNumber = Application.WorksheetFunction.Max(myRange)
For Each Cell In myRange
If Cell < 0 Then
Count = Application.WorksheetFunction.CountIf(myRange, Cell.Value)
If Count 2 And Cell.Value < LowestNumber Then
LowestNumber = Cell.Value
End If
End If
Next Cell
LowestRepeatableNumber = LowestNumber
End Function

--
HTH...

Jim Thomlinson


"Riddler" wrote:

I am trying to come up with a function or some way to return the lowest
repeatable number in a range of numbers. Here is a function that I
have. It works but does not look pretty. Does anyone have a better
routine or idea to acomplish this?

Thanks
Scott

Function LowestRepeatableNumber(myRange As Range)
Dim LastCount As Integer
Dim LowestNumber
LastCount = 0
LowestNumber = Application.WorksheetFunction.Max(myRange)
For Each Value In myRange
If Value = 0 Then GoTo 10
b = Application.WorksheetFunction.CountIf(myRange, Value)
If b 2 And Value < LowestNumber Then
LowestNumber = Value
End If
10 Next Value
LowestRepeatableNumber = LowestNumber
End Function

Thanks for the help. I was just wanting to make sure that I was not
overlooking some function or method that would better solve this
problem or make it cleaner looking.

Scott

Actually, I got his as very very slightly faster than yours.

--
Regards,
Tom Ogilvy



"Jim Thomlinson" wrote:

The logic is good but the syntax could be cleaned up a little. Be sure to
declare all of your variables and the return value of your function. Try to
avoid Goto's as they can make things very convoluted. Beyond that I would not
use Value as a variable as it is a reserved word in VBA... So something more
like this

Function LowestRepeatableNumber(myRange As Range) As Long
Dim LowestNumber As Long
Dim Cell As Range
Dim Count As Long

LowestNumber = Application.WorksheetFunction.Max(myRange)
For Each Cell In myRange
If Cell < 0 Then
Count = Application.WorksheetFunction.CountIf(myRange, Cell.Value)
If Count 2 And Cell.Value < LowestNumber Then
LowestNumber = Cell.Value
End If
End If
Next Cell
LowestRepeatableNumber = LowestNumber
End Function

--
HTH...

Jim Thomlinson


"Riddler" wrote:

I am trying to come up with a function or some way to return the lowest
repeatable number in a range of numbers. Here is a function that I
have. It works but does not look pretty. Does anyone have a better
routine or idea to acomplish this?

Thanks
Scott

Function LowestRepeatableNumber(myRange As Range)
Dim LastCount As Integer
Dim LowestNumber
LastCount = 0
LowestNumber = Application.WorksheetFunction.Max(myRange)
For Each Value In myRange
If Value = 0 Then GoTo 10
b = Application.WorksheetFunction.CountIf(myRange, Value)
If b 2 And Value < LowestNumber Then
LowestNumber = Value
End If
10 Next Value
LowestRepeatableNumber = LowestNumber
End Function

That surproses me. There might be a slight advantage to the Goto but the lack
of declarations should drag the speed down as the varaibles end up as
variants. If Speed is the overriding factor then keep the goto (Shudder) but
declare the variables.

Breaking the rules some times makes things go faster. The toughest rule to
master is when to break the rules. Just my two cents...
--
HTH...

Jim Thomlinson


"Tom Ogilvy" wrote:

Actually, I got his as very very slightly faster than yours.

--
Regards,
Tom Ogilvy



"Jim Thomlinson" wrote:

The logic is good but the syntax could be cleaned up a little. Be sure to
declare all of your variables and the return value of your function. Try to
avoid Goto's as they can make things very convoluted. Beyond that I would not
use Value as a variable as it is a reserved word in VBA... So something more
like this

Function LowestRepeatableNumber(myRange As Range) As Long
Dim LowestNumber As Long
Dim Cell As Range
Dim Count As Long

LowestNumber = Application.WorksheetFunction.Max(myRange)
For Each Cell In myRange
If Cell < 0 Then
Count = Application.WorksheetFunction.CountIf(myRange, Cell.Value)
If Count 2 And Cell.Value < LowestNumber Then
LowestNumber = Cell.Value
End If
End If
Next Cell
LowestRepeatableNumber = LowestNumber
End Function

--
HTH...

Jim Thomlinson


"Riddler" wrote:

I am trying to come up with a function or some way to return the lowest
repeatable number in a range of numbers. Here is a function that I
have. It works but does not look pretty. Does anyone have a better
routine or idea to acomplish this?

Thanks
Scott

Function LowestRepeatableNumber(myRange As Range)
Dim LastCount As Integer
Dim LowestNumber
LastCount = 0
LowestNumber = Application.WorksheetFunction.Max(myRange)
For Each Value In myRange
If Value = 0 Then GoTo 10
b = Application.WorksheetFunction.CountIf(myRange, Value)
If b 2 And Value < LowestNumber Then
LowestNumber = Value
End If
10 Next Value
LowestRepeatableNumber = LowestNumber
End Function

Not declaring the variables as the correct type incurred about a 5% penalty
(testing just with your code - whether explicitly declared as variant or with
no declaration at all).

Some Adages (I am not sure any of these could be called rules - rule
violations don't compile or run) are designed for maintainabilty rather than
speed.

--
Regards,
Tom Ogilvy


"Jim Thomlinson" wrote:

That surproses me. There might be a slight advantage to the Goto but the lack
of declarations should drag the speed down as the varaibles end up as
variants. If Speed is the overriding factor then keep the goto (Shudder) but
declare the variables.

Breaking the rules some times makes things go faster. The toughest rule to
master is when to break the rules. Just my two cents...
--
HTH...

Jim Thomlinson


"Tom Ogilvy" wrote:

Actually, I got his as very very slightly faster than yours.

--
Regards,
Tom Ogilvy



"Jim Thomlinson" wrote:

The logic is good but the syntax could be cleaned up a little. Be sure to
declare all of your variables and the return value of your function. Try to
avoid Goto's as they can make things very convoluted. Beyond that I would not
use Value as a variable as it is a reserved word in VBA... So something more
like this

Function LowestRepeatableNumber(myRange As Range) As Long
Dim LowestNumber As Long
Dim Cell As Range
Dim Count As Long

LowestNumber = Application.WorksheetFunction.Max(myRange)
For Each Cell In myRange
If Cell < 0 Then
Count = Application.WorksheetFunction.CountIf(myRange, Cell.Value)
If Count 2 And Cell.Value < LowestNumber Then
LowestNumber = Cell.Value
End If
End If
Next Cell
LowestRepeatableNumber = LowestNumber
End Function

--
HTH...

Jim Thomlinson


"Riddler" wrote:

I am trying to come up with a function or some way to return the lowest
repeatable number in a range of numbers. Here is a function that I
have. It works but does not look pretty. Does anyone have a better
routine or idea to acomplish this?

Thanks
Scott

Function LowestRepeatableNumber(myRange As Range)
Dim LastCount As Integer
Dim LowestNumber
LastCount = 0
LowestNumber = Application.WorksheetFunction.Max(myRange)
For Each Value In myRange
If Value = 0 Then GoTo 10
b = Application.WorksheetFunction.CountIf(myRange, Value)
If b 2 And Value < LowestNumber Then
LowestNumber = Value
End If
10 Next Value
LowestRepeatableNumber = LowestNumber
End Function

With the last twist of wanting to return the second lowest number if
there is no repeating numbers. I came up with the following code from
some posts above.

Function LowestRepeatableNumber(myRange As Range)
'This function returns the second lowest number above zero if there are
no repeating numbers
LowestRepeatableNumber = Evaluate("MIN(IF(" & myRange.Address &
"0," & "IF(COUNTIF(" & myRange.Address & "," & myRange.Address &
")1," & _
myRange.Address & ")))")
If LowestRepeatableNumber = 0 Then LowestRepeatableNumber =
Evaluate("small(IF(" & myRange.Address & "0," & "IF(COUNTIF(" &
myRange.Address & "," & myRange.Address & ")=1," & _

myRange.Address & ")),2)")
End Function

It works great but with one problem. I have a sheet that I use a input
box to change a number in one of the cells that this function works on.
My problem is that when I run the other macro and enter this new
number, the function runs before this new number is entered and
calculates a new number that gets used in the function. If I edit the
number again and give it the same number it all calculates right but
with this extra step. Hitting F9 to re-cacl doesnt help.
To better describe my problem, imagine cell(A1) =10 and cell(A2) =A1
* 2 this caries on for columns A-D. The function I have created finds
the lowest repeatable in the range (A2:D2). When I use my macro to edit
the value in cell(A1), it enters this new value, evaluates the function
and then exits. The value of the function does not take into account
the new value in cell (A2).

Is there some way to have the formulas update before my function does?
I tried Caclulate at the end of my macro and function with no success.

THanks
Scott

What is it supposed to do or what exactly is the lowest repeatable number?
It is the repeatable bit I don't get.

RBS

"Riddler" wrote in message
ups.com...
I am trying to come up with a function or some way to return the lowest
repeatable number in a range of numbers. Here is a function that I
have. It works but does not look pretty. Does anyone have a better
routine or idea to acomplish this?

Thanks
Scott

Function LowestRepeatableNumber(myRange As Range)
Dim LastCount As Integer
Dim LowestNumber
LastCount = 0
LowestNumber = Application.WorksheetFunction.Max(myRange)
For Each Value In myRange
If Value = 0 Then GoTo 10
b = Application.WorksheetFunction.CountIf(myRange, Value)
If b 2 And Value < LowestNumber Then
LowestNumber = Value
End If
10 Next Value
LowestRepeatableNumber = LowestNumber
End Function

What I am looking to return is the lowest number in a range that
repeats but not zero. Like if you have the following:
2
3
5
4
3
3
6
8
3
0
0
0


I want it to return 3
2 is the lowest number(excluding zero) but it only occurs once. I dont
care if it repeats more than twice just as long as it is the lowest.
I use this for timing sheets where we time processes and then we use
the lowest repeating timing that we opserved for a process.

Scott

OK, I got it.
This is another way then:

Function LowestRepeatedNumberInRange(rngRange As Range) As Long

Dim i As Long
Dim lMin As Long
Dim collDupTest As Collection
Dim arr

arr = rngRange
Set collDupTest = New Collection
lMin = WorksheetFunction.Max(rngRange)

On Error Resume Next
For i = 1 To UBound(arr)
If arr(i, 1) 0 Then
If arr(i, 1) < lMin Then
collDupTest.Add arr(i, 1), CStr(arr(i, 1))
If Err.Number < 0 Then
lMin = arr(i, 1)
End If
End If
End If
Next

LowestRepeatedNumberInRange = lMin

End Function

RBS


"Riddler" wrote in message
ups.com...
What I am looking to return is the lowest number in a range that
repeats but not zero. Like if you have the following:
2
3
5
4
3
3
6
8
3
0
0
0


I want it to return 3
2 is the lowest number(excluding zero) but it only occurs once. I dont
care if it repeats more than twice just as long as it is the lowest.
I use this for timing sheets where we time processes and then we use
the lowest repeating timing that we opserved for a process.

Scott

I ran your (LowestRepeatedNumberInRange) function and it returned the
highest number in a range only.
Like Range( 0,0,2,3,2,4) it returned 4


Scott

Works fine here.
Maybe you had a horizontal, one row range.
I tested with a vertical, one column range.

RBS

"Riddler" wrote in message
oups.com...
I ran your (LowestRepeatedNumberInRange) function and it returned the
highest number in a range only.
Like Range( 0,0,2,3,2,4) it returned 4


Scott

this should fix it:

Function LowestRepeatableNumber3(rngRange As Range) As Long

Dim i As Long
Dim lMin As Long
Dim collDupTest As Collection
Dim arr

arr = rngRange
Set collDupTest = New Collection
lMin = WorksheetFunction.Max(rngRange)

On Error Resume Next
For i = 1 To UBound(arr)
If arr(i, 1) 0 Then
If arr(i, 1) < lMin Then
collDupTest.Add arr(i, 1), CStr(arr(i, 1))
If Err.Number < 0 Then
lMin = arr(i, 1)
End If
Err.Clear
End If
End If
Next

LowestRepeatableNumber3 = lMin

End Function

--
Regards,
Tom Ogilvy


"Riddler" wrote:

I ran your (LowestRepeatedNumberInRange) function and it returned the
highest number in a range only.
Like Range( 0,0,2,3,2,4) it returned 4


Scott

Try this one:

Function LowestRepeatedNumberInRange(rngRange As Range) As Long

Dim i As Long
Dim lMin As Long
Dim collDupTest As Collection
Dim arr

arr = rngRange
Set collDupTest = New Collection
lMin = WorksheetFunction.Max(rngRange)

If rngRange.Rows.Count = 1 Then
arr = WorksheetFunction.Transpose(arr)
End If

On Error Resume Next
For i = 1 To UBound(arr)
If arr(i, 1) 0 Then
If arr(i, 1) < lMin Then
collDupTest.Add arr(i, 1), CStr(arr(i, 1))
If Err.Number < 0 Then
lMin = arr(i, 1)
End If
End If
End If
Next

LowestRepeatedNumberInRange = lMin

End Function

You don't really need to transfer to an array, but I was thinking about
making
it as fast as possible. To get the lowest repeated number is easy, but to
get it as fast
as possible is more interesting. Of course you may only have a small range
and speed
may not matter.

RBS



"Riddler" wrote in message
oups.com...
I ran your (LowestRepeatedNumberInRange) function and it returned the
highest number in a range only.
Like Range( 0,0,2,3,2,4) it returned 4


Scott

This is a better one:

Function LowestRepeatedNumberInRange2(rngRange As Range) As Long

Dim r As Long
Dim c As Long
Dim arr
Dim lCurrent As Long
Dim lMin As Long
Dim collDupTest As Collection

arr = rngRange
Set collDupTest = New Collection
lMin = WorksheetFunction.Max(arr)

On Error Resume Next
For r = 1 To UBound(arr)
For c = 1 To UBound(arr, 2)
lCurrent = arr(r, c)
If lCurrent < lMin Then
If lCurrent 0 Then
collDupTest.Add lCurrent, CStr(lCurrent)
If Err.Number < 0 Then
lMin = lCurrent
End If
End If
End If
Next
Next

LowestRepeatedNumberInRange2 = lMin

End Function

By rearranging the order of the If conditions you could make it faster,
depending on your expected data.


RBS


"Riddler" wrote in message
oups.com...
I ran your (LowestRepeatedNumberInRange) function and it returned the
highest number in a range only.
Like Range( 0,0,2,3,2,4) it returned 4


Scott

Just for in case there are no duplicates, presuming that
there won't be negative numbers:

Function LowestRepeatedNumberInRange2(rngRange As Range) As Long

Dim r As Long
Dim c As Long
Dim arr
Dim lCurrent As Long
Dim lMin As Long
Dim collDupTest As Collection
Dim bDupFound As Boolean

arr = rngRange
Set collDupTest = New Collection
lMin = WorksheetFunction.Max(arr)

On Error Resume Next
For r = 1 To UBound(arr)
For c = 1 To UBound(arr, 2)
lCurrent = arr(r, c)
If lCurrent < lMin Then
If lCurrent 0 Then
collDupTest.Add lCurrent, CStr(lCurrent)
If Err.Number < 0 Then
lMin = lCurrent
bDupFound = True
End If
End If
End If
Next
Next

If bDupFound Then
LowestRepeatedNumberInRange2 = lMin
Else
LowestRepeatedNumberInRange2 = -1
End If

End Function

I have done a bit of speed testing and I believe that a function such as the
above is
very much faster than one based on worksheet functions.
In case you wanted to test use something like this:

Option Explicit
Private Declare Function timeGetTime Lib "winmm.dll" () As Long
Private lStartTime As Long

Sub StartSW()
lStartTime = timeGetTime()
End Sub

Sub StopSW(Optional ByRef strMessage As Variant = "")
MsgBox "Done in " & timeGetTime() - lStartTime & " msecs", , strMessage
End Sub


Sub test()

Dim i As Long
Dim lResult As Long

StartSW

For i = 0 To 1000
'lResult = LowestRepeatableNumber(Selection)
lResult = LowestRepeatedNumberInRange2(Selection)
Next

StopSW

MsgBox lResult

End Sub


RBS


"Riddler" wrote in message
oups.com...
I ran your (LowestRepeatedNumberInRange) function and it returned the
highest number in a range only.
Like Range( 0,0,2,3,2,4) it returned 4


Scott

My logic in the above functions has some problems. The function below
has better logic to it. It will return "" is there is no matching
lowest repeatable number and exclude zeros.

Function LowestRepeatableNumber(myRange As Range)
Dim LowestNumber
LowestNumber = ""
For Each Cell In myRange
If Cell < 0 Then
If Application.WorksheetFunction.CountIf(myRange, Cell) =
2 Then
If Cell < LowestNumber Then LowestNumber = Cell
End If
End If
Next Cell
LowestRepeatableNumber = LowestNumber
End Function


Scott

Scott,

Function LowestRepeatableNumber(myRange As Range)
LowestRepeatableNumber = Evaluate("MIN(IF(" & myRange.Address & "0," & _
"IF(COUNTIF(" & myRange.Address & "," & myRange.Address & ")1," & _
myRange.Address & ")))")
End Function

Or use this version if negative numbers are allowed:

Function LowestRepeatableNumber(myRange As Range)
LowestRepeatableNumber = Evaluate("MIN(IF(" & myRange.Address & "<0," & _
"IF(COUNTIF(" & myRange.Address & "," & myRange.Address & ")1," & _
myRange.Address & ")))")
End Function


HTH,
Bernie
MS Excel MVP


"Riddler" wrote in message
ups.com...
I am trying to come up with a function or some way to return the lowest
repeatable number in a range of numbers. Here is a function that I
have. It works but does not look pretty. Does anyone have a better
routine or idea to acomplish this?

Thanks
Scott

Function LowestRepeatableNumber(myRange As Range)
Dim LastCount As Integer
Dim LowestNumber
LastCount = 0
LowestNumber = Application.WorksheetFunction.Max(myRange)
For Each Value In myRange
If Value = 0 Then GoTo 10
b = Application.WorksheetFunction.CountIf(myRange, Value)
If b 2 And Value < LowestNumber Then
LowestNumber = Value
End If
10 Next Value
LowestRepeatableNumber = LowestNumber
End Function

This version will return "" if there are no repeats:

Function LowestRepeatableNumber(myRange As Range)
LowestRepeatableNumber = Evaluate("MIN(IF(" & myRange.Address & "0," & _
"IF(COUNTIF(" & myRange.Address & "," & myRange.Address & ")1," & _
myRange.Address & ")))")
If LowestRepeatableNumber = 0 Then LowestRepeatableNumber = ""
End Function

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Scott,

Function LowestRepeatableNumber(myRange As Range)
LowestRepeatableNumber = Evaluate("MIN(IF(" & myRange.Address & "0," & _
"IF(COUNTIF(" & myRange.Address & "," & myRange.Address & ")1," & _
myRange.Address & ")))")
End Function

Or use this version if negative numbers are allowed:

Function LowestRepeatableNumber(myRange As Range)
LowestRepeatableNumber = Evaluate("MIN(IF(" & myRange.Address & "<0," & _
"IF(COUNTIF(" & myRange.Address & "," & myRange.Address & ")1," & _
myRange.Address & ")))")
End Function


HTH,
Bernie
MS Excel MVP


"Riddler" wrote in message
ups.com...
I am trying to come up with a function or some way to return the lowest
repeatable number in a range of numbers. Here is a function that I
have. It works but does not look pretty. Does anyone have a better
routine or idea to acomplish this?

Thanks
Scott

Function LowestRepeatableNumber(myRange As Range)
Dim LastCount As Integer
Dim LowestNumber
LastCount = 0
LowestNumber = Application.WorksheetFunction.Max(myRange)
For Each Value In myRange
If Value = 0 Then GoTo 10
b = Application.WorksheetFunction.CountIf(myRange, Value)
If b 2 And Value < LowestNumber Then
LowestNumber = Value
End If
10 Next Value
LowestRepeatableNumber = LowestNumber
End Function

I assumed a cell formula would be quicker than any UDF and came up with this

=MIN(IF(--(COUNTIF(ref,ref)1)*ref,ref))

array entered

But it seems very slow with a large range, must be a better one!

Regards,
Peter T

"Riddler" wrote in message
ups.com...
I am trying to come up with a function or some way to return the lowest
repeatable number in a range of numbers. Here is a function that I
have. It works but does not look pretty. Does anyone have a better
routine or idea to acomplish this?

Thanks
Scott

Function LowestRepeatableNumber(myRange As Range)
Dim LastCount As Integer
Dim LowestNumber
LastCount = 0
LowestNumber = Application.WorksheetFunction.Max(myRange)
For Each Value In myRange
If Value = 0 Then GoTo 10
b = Application.WorksheetFunction.CountIf(myRange, Value)
If b 2 And Value < LowestNumber Then
LowestNumber = Value
End If
10 Next Value
LowestRepeatableNumber = LowestNumber
End Function

I like the simplicity of the array formula but can it be made to return
the second smallest (non zero) number is there are no repeating ones to
return the lowest of?
I played with it a bit but did not have any luck. I need to learn more
about array formulas. It looks like they have some great potential. As
for speed of them my array of numbers is usually less than 15 numbers
so it runs plenty fast enough.

Thanks
Scott

A minor thing first, the unary minus (the double -ve) is redundant in the
previous example.

This amended array entered formula should return an error (#NUM) if there
are no duplicate non-zero numbers

=SMALL(IF((COUNTIF(ref,ref)1)*ref,ref),1)

IOW if the resultant array is all non-numeric values, ie all FALSE's, Small
fails which I think is what you want (could include an additional IFERROR
etc).

but can it be made to return
the second smallest (non zero) number is there are no repeating ones to
return the lowest of?

I'm sure it's possible, but...?

If this snippet of the above formula -
(COUNTIF(ref,ref)1)*ref
is applied to this array
{0;0;1;1;1;2;7;7}
returns
{0;0;1;1;1;0;7;7}

Would need to return the second + 1 (third) samllest unique, in the above 7.

Might be worth asking over in excel.worksheetfunctions.

Re speed, normally cell formulas are faster but these formulas are doing a
lot more work than say the UDF posted by RBS. However if as you say you only
have 15 values to process the formula should be faster due to the overhead
of even the simplest UDF.

Regards,
Peter T

"Riddler" wrote in message
ups.com...
I like the simplicity of the array formula but can it be made to return
the second smallest (non zero) number is there are no repeating ones to
return the lowest of?
I played with it a bit but did not have any luck. I need to learn more
about array formulas. It looks like they have some great potential. As
for speed of them my array of numbers is usually less than 15 numbers
so it runs plenty fast enough.

Thanks
Scott