Returning row and column of value in 2D array, with duplicates in table

[mrblister]

I have a large 2D table. The table has row and column headers. Headers are "names", so it looks like:

	Amy	Bill	Cat
Amy	1	0.5	0.6
Bill	0.5	1	0.2
Cat	0.6	0.2	1

Let's say the values are ratings (it's actually a Correl table).
I need to get a list of the (top X) highest rating-pairs in descending order (so in the above example, the list I am looking for is Amy-Cat (or Cat-Amy), then Amy-Bill (or Bill-Amy), then Cat-Bill).

But how do I do that? I can use LARGE to return a list of largest values, and I found a formula that returns row and column of a value in a table BUT it only works if there are no duplicate values in the table (but there are).
Alternatively, I tried to make it so no duplicates exist in the table by adding miniscule amounts to every value, but duplicates still occurred.

It's a pretty large table, so efficiency is a concern, although I'm definitely not picky at this point.

Any thoughts?

 

[drsarao]

Difficult with formulae especially considering big data.
VBA might help.
Try this:

Sub PickPairs()
Dim lr As Long, lc As Long, r As Long, c As Long, lrw As Long, w As Long
Dim dr 'data read
Dim dw() 'datawrite
With Worksheets("Data") 'source data sheet
    lr = .Cells(.Rows.Count, "A").End(xlUp).Row
    lc = .Cells(1, .Columns.Count).End(xlToLeft).Column
    dr = .Range(.Cells(1, 1), .Cells(lr, lc)) 'presuming all data in the sheet is table
End With
lrw = (lr - 1) * (lc - 1) + 1
ReDim dw(1 To lrw, 1 To 2)
dw(1, 1) = "Values"
dw(1, 2) = "Pairs"
w = 2
For r = 2 To lr
    For c = 2 To lc
        dw(w, 1) = dr(r, c)
        dw(w, 2) = dr(r, 1) & "-" & dr(1, c)
        w = w + 1
    Next c
Next r
With Worksheets("Result") 'result sheet
    .Range("A1").Resize(lrw, 2) = dw
    .Range("A1:B" & lrw).Sort key1:=.Range("A1"), Order1:=xlAscending, Header:=xlYes
End With
End Sub

 

[drsarao]

Change the sheet names as needed.
Change table boundaries if needed.

 

[mrblister]

Change the sheet names as needed.
Change table boundaries if needed.

Thank you!

 

[drsarao]

You are welcome!