I have a large 2D table. The table has row and column headers. Headers are "names", so it looks like:
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?
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?
Last edited: