Modified Rank function?

Juggler_IN

Board Regular
Joined
Nov 19, 2014
Messages
154
I have worked out a UDF which replicates Excel Rank() function. Now, I am trying to modify it such that instead of outputting 1 2 2 4 as the ranks for a set {2,5,5,8} it should output 1 3 3 4; and it should be able to handle any data as per the modified logic.

Code:
Public Function Rnk( _
       ByVal x As Variant, _
       ByRef y As Variant, _
       ByVal z As Boolean) As Variant

' x is the item, y is the Range, and z is the rank order.


    If TypeName(y) = "Range" Then
        y = y.Value2
    Else
    End If


    With CreateObject("System.Collections.ArrayList")


        Dim i As Variant
        For Each i In y
            If VarType(i) >= 2# And VarType(i) <= 6# Then .Add Val(i)
        Next i


        .Sort


        If z = False Then    ' Descending Order
            .Reverse
        Else    ' Ascending Order
        End If


        Rnk = Application.WorksheetFunction.Match(x, .ToArray, 0#) + 1


    End With


End Function
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,252
Office Version
2013
Platform
Windows
Try using "not exact match" in the Match Fuction (i.e 1) & remove the + 1

Code:
Rnk = Application.WorksheetFunction.Match(x, .ToArray, 1) '[COLOR=#ff0000]''+ 1[/COLOR]
 

Juggler_IN

Board Regular
Joined
Nov 19, 2014
Messages
154
Also, there is a typo in my original code:
Code:
[COLOR=#333333]        Rnk = Application.WorksheetFunction.Match(x, .ToArray, 0#) + 1[/COLOR]
Should be,
Code:
[COLOR=#333333]        Rnk = Application.WorksheetFunction.Match(x, .ToArray, 0#)[/COLOR]
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
6,910
Office Version
2019
Platform
Windows
Do you really need a udf for this?

For ascending order,

Original logic =COUNTIFS($A$2:$A$5,"<"&A2)+1
New logic =COUNTIFS($A$2:$A$5,"<="&A2)

change < to > for descending.

edit:- for the udf, try
Code:
Rnk = Application.WorksheetFunction.Match(x, .ToArray, IIf(z = False, -1, 1))
 
Last edited:

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
6,910
Office Version
2019
Platform
Windows
Repeating my previous question which you appear to have overlooked, Do you really need a udf?

You can do what you want with native excel functions, there is no point in trying to re-invent the wheel!

Bearing in mind that I can not read your mind or see your screen! What does 'not working' mean? Your logic is illogical so False might need to changing to True.

Runtime error? Wrong results? Something else?

Your logic is illogical so False might need to changing to True.
 

Juggler_IN

Board Regular
Joined
Nov 19, 2014
Messages
154
@jasonb75;

With z = False, it "still" gives 4 2 2 1 as the output instead of 4 3 3 1.
With z = True, it gives 1 3 3 4 as the output which is desired.
 

Forum statistics

Threads
1,077,959
Messages
5,337,413
Members
399,144
Latest member
Lauren Ward

Some videos you may like

This Week's Hot Topics

Top