Finding highest value in array

[Excel Fox]

But I don't see that a pair of Obs are always same. There are some obs which are not following that rule. Is that something wrong with the matrix or is it legit?

[Rasm]

Yes - the matrix is legit - not sure why you can not find reversed pairs - but just set the ShowSingle to True - I properly should not have given the option of making it double sided

If ShowSingle Then
                                .Cells(ii + 2, i + 2) = CorrelMatrix(i + 1, ii + 1)
                                .Cells(i + 2, ii + 2) = vbNullString
                            Else
                                .Cells(i + 2, ii + 2) = CorrelMatrix(i + 1, ii + 1)
                                .Cells(ii + 2, i + 2) = CorrelMatrix(i + 1, ii + 1)
 End If

[excelgeek]

Assuming a 1-based array, you could use:

for n = lbound(correlmatrix, 2) to ubound(correlmatrix, 2)
varmatch = application.match(avar, application.index(correlmatrix, 0, n))
if not iserror(varmatch) then
   msgbox "x=" & varmatch & "; y=" & n
end if
next n

though I suspect it would be as fast if not faster to simply iterate every element rather than using worksheet functions on arrays.

[Rasm]

Excelgeek
I will try it - but I think you are right about simply using a loop and find it that way instead - it may be faster with the loop - thanks.

[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

[Rasm]

Been away - just seen this - I will try it - thanks