VBA - Get index of nth largest value in an array

Question

I want to find the index of the nth largest value in an array. I can do the following but it runs into trouble when 2 values are equal.

fltArr(0)=31
fltArr(1)=15
fltArr(2)=31
fltArr(3)=52

For i = 0 To UBound(fltArr)
    If fltArr(i) = Application.WorksheetFunction.Large(fltArr, n) Then
        result = i
    End If
Next

n=1 ---> 3
n=2 ---> 2 (but I want this to be 0)
n=3 ---> 2
n=4 ---> 1

Solution 1

Uses a second array to quickly get what you want without looping through each element for every value of n

Sub test()

Dim fltArr(0 To 3)
Dim X
Dim n As Long
Dim lngPos As Long

fltArr(0) = 31
fltArr(1) = 15
fltArr(2) = 31
fltArr(3) = 52

X = fltArr

For n = 1 To 4
    lngPos = Application.WorksheetFunction.Match(Application.Large(X, n), X, 0) - 1
    Debug.Print lngPos
    X(lngPos) = Application.Max(X)
Next

End Sub

Solution 2

Edit:

Public Sub RunLarge()
Dim n%, i%, result%, count%
Dim fltArr(3) As Integer
Dim iLarge As Integer

fltArr(0) = 31:
fltArr(1) = 15:
fltArr(2) = 31:
fltArr(3) = 52
n = 1

Debug.Print " n", "iLarge", "result"

While n <= 4
    count% = n - 1
    iLarge = Application.WorksheetFunction.Large(fltArr, n)

    For i = 0 To UBound(fltArr)
        If fltArr(i) = iLarge Then
            result = i
            count% = count% - 1
            If count% <= 0 Then Exit For
        End If
    Next

    Debug.Print n, iLarge, result
    n = n + 1
Wend
End Sub

result:

 n            iLarge        result
 1             52            3 
 2             31            0 
 3             31            2 
 4             15            1 

Comments

[doovers]

Apologies, I wrote the post too quick and I made a mistake there. The results you got were same as mine! I'll edit question now!

[doovers]

Good idea but unfortunately the dataset can't be modified

[doovers]

Nice one mate, I reckon you've cracked it! Gonna go run some tests and I'll be back to mark it as the answer!

[El Scripto]

I love the idea, however I think it might have a problem: when starting from n = 2 (or X = 2 in this case), then the largest number will be 52, not 31.

[Dan Donoghue]

If the dataset can be modified, you could always copy it to a second array and work on that, any modifications make no difference as your original array would still be intact. El Scipto. Good point :)

[doovers]

Although your answer work perfectly, I marked brettdj's post as the answer because in my case where the array is very large, his solution performs a lot better since there is only one loop. Thanks for the help though!

[Przemyslaw Remin]

Can someone please explain how it works? What is that for X(lngPos) = Application.Max(X)?

[T.M.]

How it works: X is a helper array where the greatest element found latest therein is afterwards overwritten by the maximum value of 52. So in each loop there are fewer elements to check via Match for a nth- Large value.