Nth Largest Unique Value in an Array (UDF)

[Admin]

Hi All,

Here is an UDF to find the nth Largest unique value from an array.

Function NTHLARGESTUNIQUE(ByRef InpData, Optional ByVal Nth As Long = 1)
    
    Dim i   As Long, UB1 As Long, UB2 As Long
    
    NTHLARGESTUNIQUE = CVErr(xlErrNum)
    If TypeOf InpData Is Range Then
        If InpData.Rows.Count > 1 And InpData.Columns.Count = 1 Then
            InpData = Application.Transpose(InpData.Value2)
        ElseIf InpData.Rows.Count = 1 And InpData.Columns.Count > 1 Then
            InpData = Application.Transpose(Application.Transpose(InpData.Value2))
        Else
            Exit Function
        End If
    End If
    
    On Error Resume Next
    UB1 = UBound(InpData, 1)
    UB2 = UBound(InpData, 2)
    On Error GoTo 0
    
    If UB1 > 0 And UB2 > 0 Then Exit Function
    
    With CreateObject("system.collections.sortedlist")
        For i = LBound(InpData) To UBound(InpData)
            .Item(InpData(i)) = Empty
        Next
        If .Count Then
            NTHLARGESTUNIQUE = .getkey(.Count - Nth)
        End If
    End With
    
End Function

Use like

=NTHLARGESTUNIQUE(A1:A15,2)

or

=NTHLARGESTUNIQUE(A1:G1,2)

or

MsgBox NTHLARGESTUNIQUE([{1,2,5,8,4}], 3)

BTW, for those who want to know about system.collections.sortedlist, find this MSDN link:


SortedList Class https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA