Finding highest value in array

[Admin]

Hi Rasm,

It's not a bad idea to write the array in a worksheet, use some formulas and get back the results in an array.

Here is a routing which might help you.

Function SORTMATRIX(ByRef Matrix, ByRef ObvsDown, ByRef ObvsAcross) As Variant
    
    Dim UB1         As Long
    Dim UB2         As Long
    Dim strMatrix   As String
    Dim strObvsD    As String
    Dim strObvsA    As String
    Dim strMaxVals  As String
    Dim strRank     As String
    
    Dim wksTemp     As Worksheet
    
    With Application
        .ScreenUpdating = 0
        .DisplayAlerts = 0
    End With
    
    If TypeOf Matrix Is Range Then Matrix = Matrix.Value
    If TypeOf ObvsDown Is Range Then ObvsDown = ObvsDown.Value
    If TypeOf ObvsAcross Is Range Then ObvsAcross = ObvsAcross.Value
    
    
    Set wksTemp = ThisWorkbook.Worksheets.Add
    
    UB1 = UBound(Matrix, 1)
    UB2 = UBound(Matrix, 2)
    
    With wksTemp
        .Range("b2").Resize(UB1, UB2).Value = Matrix
        .Range("a2").Resize(UB1).Value = ObvsDown
        .Range("b1").Resize(, UB2).Value = ObvsAcross
        .Range("a:c").EntireColumn.Insert
        strMatrix = .Range("e2").Resize(UB1, UB2).Address
        strObvsD = .Range("d2").Resize(UB1).Address
        strObvsA = .Range("e1").Resize(, UB2).Address
        strMaxVals = .Range("b2").Resize(UB1).Address
        strRank = .Range("c2").Resize(UB1).Address
        
        .Range("b2").Resize(UB1).Formula = "=MAX(INDEX(" & strMatrix & ",0,MATCH(d2," & strObvsA & ",0)))"
        .Range("a2").Resize(UB1).Formula = "=INDEX(" & strObvsD & ",MATCH(b2,INDEX(" & strMatrix & ",0,MATCH(d2," & strObvsA & ",0)),0))"
        .Range("c2").Resize(UB1).Formula = "=RANK(B2," & strMaxVals & ")+COUNTIF($B$2:B2,B2)-1"
        
        With .Range("a2").Resize(UB1, 3)
            .Value = .Value2
            .Sort .Cells(1, 2), 2, Header:=2
            SORTMATRIX = .Cells(1).Resize(UB1, 2)
        End With
    End With
    wksTemp.Delete
    
    With Application
        .ScreenUpdating = 1
        .DisplayAlerts = 1
    End With
    
End Function

and call

Sub kTest()
    
    Dim a
    
    a = SORTMATRIX([b2:u21], [a2:a21], [b1:u1])'the range could be CorrelMatrix
    
End Sub