Results 1 to 5 of 5

Thread: Nth Largest Unique Value in an Array (UDF)

  1. #1
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10

    Lightbulb Nth Largest Unique Value in an Array (UDF)

    Hi All,

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

    Code:
    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

    Code:
    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
    Last edited by DocAElstein; 06-10-2023 at 01:33 PM.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Admin View Post
    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(InpDat a.Value2))
    Else
    Exit Function
    End If
    End If
    ....
    .... < snip >
    ....
    Just wanted to point out that the double call to Application.Transpose (highlighted in bold) can be replaced with a single call to Application.Index (which should also be faster)...

    InpData = Application.Index(InpData.Value2, 1, 0)


    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 06-10-2023 at 01:38 PM.

  3. #3
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Thanks Rick
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  4. #4
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Admin View Post
    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...

    Code:
    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...

    Code:
    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

  5. #5
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    In general, we look these kind of things in either on a single column or row. In fact I made this UDF for someone who asked this question somewhere on net.

    Thanks for making this UDF more generic
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. Nth Such-And-Such Day Of The Month
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 6
    Last Post: 01-02-2020, 06:48 PM
  2. Fetch Nth last item from a list
    By SDruley in forum Excel Help
    Replies: 2
    Last Post: 01-01-2013, 09:28 AM
  3. Nth Working Day Including Saturday
    By Excel Fox in forum Download Center
    Replies: 0
    Last Post: 10-10-2012, 02:41 AM
  4. Replies: 2
    Last Post: 01-07-2012, 12:11 AM
  5. Average Of The X Largest Numbers
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 04-16-2011, 04:15 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •