Nth Largest Unique Value in an Array (UDF)

[Admin]

Thanks Rick

[Rick Rothstein]

Thanks Rick

You are welcome, of course. I looked at your function in a little more depth and am not sure why you restricted it to only (contiguous) numerical data in single column ranges or single row ranges. If I am not mistaken, I believe the following will work correctly for (contiguous) numerical data in single column ranges, single row ranges OR rectangular ranges...

Function NTHLARGESTUNIQUE(InpData As Variant, Optional ByVal Nth As Long = 1) As Variant
  Dim V As Variant, Arry As Variant
  NTHLARGESTUNIQUE = CVErr(xlErrNum)
  On Error GoTo Whoops
  Arry = InpData
  With CreateObject("System.Collections.SortedList")
    For Each V In Arry
      .Item(V) = Empty
    Next
    If .Count Then NTHLARGESTUNIQUE = .getkey(.Count - Nth)
  End With
Whoops:
End Function

And if you wanted to allow the function to ignore errors, blanks and text within the range, then I believe you could do this instead...

Function NTHLARGESTUNIQUE(InpData As Variant, Optional ByVal Nth As Long = 1) As Variant
  Dim V As Variant, Arry As Variant
  NTHLARGESTUNIQUE = CVErr(xlErrValue)
  On Error GoTo Whoops
  Arry = InpData
  With CreateObject("System.Collections.SortedList")
    For Each V In Arry
      If IsNumeric(V) Then .Item(V) = Empty
    Next
    If .Count Then NTHLARGESTUNIQUE = .getkey(.Count - Nth)
  End With
Whoops:
End Function