Search code examples
excelexcel-formulapivot-tableworksheet-functionunpivot

Ranking two dimensions of data

I have a set of data in two dimensions, like so:

      A     B     C

 A    -     9     4

 B    24    -     13

 C    3     12    -

It represents relationships between two entities. I would like to return a list of those values ranked, such as: 

AB:4
AC:5
BA:1
BC:2
CA:6
CB:3

Any thoughts on the best way to approach this?

Solution

  • Create a copy of your source matrix with, assuming layout as below, 

    =IFERROR(RANK(B2,$B$2:$D$4),"")

    in G2 copied down and across to I4. From this 2D version of your rankings create a PivotTable with multiple consolidation ranges ("reverse pivot" - maybe Alt+D, P). Double click on the Totals intersect. If results are copied back in to source sheet as below (for convenience), add =Q2&R2&": "&S2 in U2 (or adjust accordingly) and copy down to suit:

    SO18387948 example

    May be more appropriate for larger datasets!