Order Data UDF

[Juggler_IN]

I wanted help in modifying a RANK UDF to output ORDER values instead of RANK values as per the attached image. Thereby, instead of ranks 2,3,1 for data 5,6,4, the UDF should output the order 3,1,2.

The custom Rank UDF is:

Public Function Rankvalue( _
       ByVal u As Variant, _
       ByRef v As Variant, _
       Optional ByVal w As Boolean = True) As Variant

    Dim x As Variant, y As Variant

    On Error GoTo ErrHlr

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

    With CreateObject("System.Collections.ArrayList")

        For Each x In v
            If VBA.VarType(x) >= 2& And VBA.VarType(x) <= 6& Then
                .Add VBA.Val(x)
            Else
                GoTo ErrHlr
            End If
        Next x

        .Sort    ' Ascending Order

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

        y = Application.WorksheetFunction.Match(u, .ToArray, 0&)
        Rankvalue = y
        Exit Function

    End With

ErrHlr:
    Rankvalue = "?"
    Exit Function

End Function

 

[mikerickson]

I looks like MATCH would do what you want

If you have 5,6,4 in A1:C1
Then MATCH(5, A1:C1, 0) will return 1, MATCH(6, A1:C1, 0) will return 2 etc

 

[Juggler_IN]

@mikerickson; ... match is not working. It outputs 1,2,3 instead of the required 3,1,2.

 

[mikerickson]

I don't understand the logic with how 5,6,4 should result in 3,1,2.

The graphic at the bottom of post 1 shows, the result 1,2,3.

 

[Juggler_IN]

@mikerickson; ... 1,2,3 is the position. If I rank the data 5,6,4 in ascending order I get the ranks as 2,3,1. If this data 5,6,4 was to be ordered the graph is giving the output of 3,1,2 under the last column titled order. It is the actual order of individual values in the master data (5=1, 6=2, 4=3) and shown against the sorted data. 4,5,6 gives (3,1,2) as 4 is at 3rd position, 5 is at first position and 6 is at third position in unsorted data.

 

[mikerickson]

To find the position in the original order, use that array in that order (cells on worksheet?) as the second argument of MATCH.