Lowest repeatable number in a range

[Tom Ogilvy]

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.

 

[Riddler]

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 (A22). 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

 

[track.hager]

Try this array formula:

=MIN(IF(IF(COUNTIF(range,range)>1,range,0)=0,"",range))

Regards,
David Hager

 

[Tom Ogilvy]

I didn't look, but maybe you need to test it more.


If you are using an error as a decision point and you don't clear it, then
it remains not equal to zero after an error has occurred in my test.

I don't know what the "function you posted last" is, so I can't say.

 

[RB Smissaert]

Yes, you are right, the error needs clearing.
Just hadn't tested right.
This is what I posted last, except Err.Clear added:

Function LowestRepeatedNumberInRange4(rngRange As Range) As Long

Dim r As Long
Dim c As Long
Dim arr
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)
If arr(r, c) < lMin Then
If arr(r, c) > 0 Then
collDupTest.Add arr(r, c), CStr(arr(r, c))
If Err.Number <> 0 Then
lMin = arr(r, c)
bDupFound = True
Err.Clear
End If
End If
End If
Next
Next

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

End Function


Unless you are going to bother with API code I think it will be difficult to
beat for speed.

RBS

 

[Peter T]

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]

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

 

[Peter T]

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