Trying to find worst and best measures from a list containing duplicates

[BrAinZ]

I am trying to do something that should be simple, by my brain is having a dumb block.

I have a table of sites showing their ranking for various performance measures.
I just want to create a list showing which is their best and worst measures, but I'm getting confused where they may be the same rank across various measures.

Would anyone please be kind enough to point me in the right direction...

[TABLE="width: 466"]
<tbody>[TR]
[TD][/TD]
[TD]Site 1[/TD]
[TD]Site 2[/TD]
[TD]Site 3[/TD]
[TD]Site 4[/TD]
[/TR]
[TR]
[TD]Measure 1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Measure 2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Measure 3[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Measure 4[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Measure 5[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Site 1[/TD]
[TD]Site 2[/TD]
[TD]Site 3[/TD]
[TD]Site 4[/TD]
[/TR]
[TR]
[TD]Best Measure[/TD]
[TD]Measure 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2nd Best Measure[/TD]
[TD]Measure 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3rd Best Measure[/TD]
[TD]Measure 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Site 1[/TD]
[TD]Site 2[/TD]
[TD]Site 3[/TD]
[TD]Site 4[/TD]
[/TR]
[TR]
[TD]Worst Measure[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2nd Wort Measure[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3rd Worst Measure[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[DanteAmor]

The formulas go

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:8pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:112.16px;" /><col style="width:78.89px;" /><col style="width:68.44px;" /><col style="width:68.44px;" /><col style="width:68.44px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:11pt; "> </td><td style="background-color:#c2d69a; font-weight:bold; font-size:11pt; text-align:center; ">Site 1</td><td style="background-color:#c2d69a; font-weight:bold; font-size:11pt; text-align:center; ">Site 2</td><td style="background-color:#c2d69a; font-weight:bold; font-size:11pt; text-align:center; ">Site 3</td><td style="background-color:#c2d69a; font-weight:bold; font-size:11pt; text-align:center; ">Site 4</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:11pt; ">Measure 1</td><td style="font-size:11pt; text-align:right; ">2</td><td style="font-size:11pt; text-align:right; ">3</td><td style="font-size:11pt; text-align:right; ">1</td><td style="font-size:11pt; text-align:right; ">4</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:11pt; ">Measure 2</td><td style="font-size:11pt; text-align:right; ">1</td><td style="font-size:11pt; text-align:right; ">2</td><td style="font-size:11pt; text-align:right; ">3</td><td style="font-size:11pt; text-align:right; ">3</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:11pt; ">Measure 3</td><td style="font-size:11pt; text-align:right; ">4</td><td style="font-size:11pt; text-align:right; ">1</td><td style="font-size:11pt; text-align:right; ">4</td><td style="font-size:11pt; text-align:right; ">1</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:11pt; ">Measure 4</td><td style="font-size:11pt; text-align:right; ">3</td><td style="font-size:11pt; text-align:right; ">2</td><td style="font-size:11pt; text-align:right; ">1</td><td style="font-size:11pt; text-align:right; ">4</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:11pt; ">Measure 5</td><td style="font-size:11pt; text-align:right; ">2</td><td style="font-size:11pt; text-align:right; ">1</td><td style="font-size:11pt; text-align:right; ">3</td><td style="font-size:11pt; text-align:right; ">4</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:11pt; "> </td><td style="background-color:#c2d69a; font-weight:bold; font-size:11pt; text-align:center; ">Site 1</td><td style="background-color:#c2d69a; font-weight:bold; font-size:11pt; text-align:center; ">Site 2</td><td style="background-color:#c2d69a; font-weight:bold; font-size:11pt; text-align:center; ">Site 3</td><td style="background-color:#c2d69a; font-weight:bold; font-size:11pt; text-align:center; ">Site 4</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:11pt; ">Best Measure</td><td style="font-size:11pt; text-align:right; ">1</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:11pt; ">2nd Best Measure</td><td style="font-size:11pt; text-align:right; ">2</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:11pt; ">3rd Best Measure</td><td style="font-size:11pt; text-align:right; ">2</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-size:11pt; "> </td><td style="background-color:#c2d69a; font-weight:bold; font-size:11pt; text-align:center; ">Site 1</td><td style="background-color:#c2d69a; font-weight:bold; font-size:11pt; text-align:center; ">Site 2</td><td style="background-color:#c2d69a; font-weight:bold; font-size:11pt; text-align:center; ">Site 3</td><td style="background-color:#c2d69a; font-weight:bold; font-size:11pt; text-align:center; ">Site 4</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="font-size:11pt; ">Best Measure</td><td style="font-size:11pt; text-align:right; ">4</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="font-size:11pt; ">2nd Best Measure</td><td style="font-size:11pt; text-align:right; ">3</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="font-size:11pt; ">3rd Best Measure</td><td style="font-size:11pt; text-align:right; ">2</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >B9</td><td >=SMALL(B$2:B$6,1)</td></tr><tr><td >B10</td><td >=SMALL(B$2:B$6,2)</td></tr><tr><td >B11</td><td >=SMALL(B$2:B$6,3)</td></tr><tr><td >B14</td><td >=LARGE(B$2:B$6,1)</td></tr><tr><td >B15</td><td >=LARGE(B$2:B$6,2)</td></tr><tr><td >B16</td><td >=LARGE(B$2:B$6,3)</td></tr></table></td></tr></table>

 

[BrAinZ]

Thanks, but that’s not quite what I need. I want it to show the “name” of the best measures and worst measures.

my problem is that one site may he say, 2nd best for three different measures, then say 4th for another measure, so the sheet needs to look up the names of the best performing measures (and also worst, which I’m sure I can work out once I get my head round it).

 

[DanteAmor]

Sorry. You're right, you asked for the name.Let's try an auxiliary column for each Site. The good news is that it is the same formula for all auxiliary columns. The same for the best and worst, is the same formula.


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:8pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:153.98px;" /><col style="width:80.79px;" /><col style="width:80.79px;" /><col style="width:80.79px;" /><col style="width:80.79px;" /><col style="width:29.47px;" /><col style="width:68.44px;" /><col style="width:68.44px;" /><col style="width:68.44px;" /><col style="width:68.44px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:11pt; "> </td><td style="background-color:#c2d69a; font-weight:bold; font-size:11pt; text-align:center; ">Site 1</td><td style="background-color:#c2d69a; font-weight:bold; font-size:11pt; text-align:center; ">Site 2</td><td style="background-color:#c2d69a; font-weight:bold; font-size:11pt; text-align:center; ">Site 3</td><td style="background-color:#c2d69a; font-weight:bold; font-size:11pt; text-align:center; ">Site 4</td><td style="font-size:11pt; "> </td><td style="background-color:#c2d69a; font-weight:bold; font-size:11pt; text-align:center; ">Site 1</td><td style="background-color:#c2d69a; font-weight:bold; font-size:11pt; text-align:center; ">Site 2</td><td style="background-color:#c2d69a; font-weight:bold; font-size:11pt; text-align:center; ">Site 3</td><td style="background-color:#c2d69a; font-weight:bold; font-size:11pt; text-align:center; ">Site 4</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:11pt; ">Measure 1</td><td style="font-size:11pt; text-align:right; ">2</td><td style="font-size:11pt; text-align:right; ">3</td><td style="font-size:11pt; text-align:right; ">1</td><td style="font-size:11pt; text-align:right; ">4</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; text-align:right; ">3</td><td style="font-size:11pt; text-align:right; ">5</td><td style="font-size:11pt; text-align:right; ">1</td><td style="font-size:11pt; text-align:right; ">3</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:11pt; ">Measure 2</td><td style="font-size:11pt; text-align:right; ">1</td><td style="font-size:11pt; text-align:right; ">2</td><td style="font-size:11pt; text-align:right; ">3</td><td style="font-size:11pt; text-align:right; ">3</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; text-align:right; ">1</td><td style="font-size:11pt; text-align:right; ">3</td><td style="font-size:11pt; text-align:right; ">3</td><td style="font-size:11pt; text-align:right; ">2</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:11pt; ">Measure 3</td><td style="font-size:11pt; text-align:right; ">1</td><td style="font-size:11pt; text-align:right; ">1</td><td style="font-size:11pt; text-align:right; ">4</td><td style="font-size:11pt; text-align:right; ">1</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; text-align:right; ">2</td><td style="font-size:11pt; text-align:right; ">1</td><td style="font-size:11pt; text-align:right; ">5</td><td style="font-size:11pt; text-align:right; ">1</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:11pt; ">Measure 4</td><td style="font-size:11pt; text-align:right; ">3</td><td style="font-size:11pt; text-align:right; ">2</td><td style="font-size:11pt; text-align:right; ">1</td><td style="font-size:11pt; text-align:right; ">4</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; text-align:right; ">5</td><td style="font-size:11pt; text-align:right; ">4</td><td style="font-size:11pt; text-align:right; ">2</td><td style="font-size:11pt; text-align:right; ">4</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:11pt; ">Measure 5</td><td style="font-size:11pt; text-align:right; ">2</td><td style="font-size:11pt; text-align:right; ">1</td><td style="font-size:11pt; text-align:right; ">3</td><td style="font-size:11pt; text-align:right; ">4</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; text-align:right; ">4</td><td style="font-size:11pt; text-align:right; ">2</td><td style="font-size:11pt; text-align:right; ">4</td><td style="font-size:11pt; text-align:right; ">5</td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:11pt; "> </td><td style="background-color:#c2d69a; font-weight:bold; font-size:11pt; text-align:center; ">Site 1</td><td style="background-color:#c2d69a; font-weight:bold; font-size:11pt; text-align:center; ">Site 2</td><td style="background-color:#c2d69a; font-weight:bold; font-size:11pt; text-align:center; ">Site 3</td><td style="background-color:#c2d69a; font-weight:bold; font-size:11pt; text-align:center; ">Site 4</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:11pt; ">Best Measure</td><td style="font-size:11pt; ">Measure 2</td><td style="font-size:11pt; ">Measure 3</td><td style="font-size:11pt; ">Measure 1</td><td style="font-size:11pt; ">Measure 3</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:11pt; ">2nd Best Measure</td><td style="font-size:11pt; ">Measure 3</td><td style="font-size:11pt; ">Measure 5</td><td style="font-size:11pt; ">Measure 4</td><td style="font-size:11pt; ">Measure 2</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:11pt; ">3rd Best Measure</td><td style="font-size:11pt; ">Measure 1</td><td style="font-size:11pt; ">Measure 2</td><td style="font-size:11pt; ">Measure 2</td><td style="font-size:11pt; ">Measure 1</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-size:11pt; "> </td><td style="background-color:#c2d69a; font-weight:bold; font-size:11pt; text-align:center; ">Site 1</td><td style="background-color:#c2d69a; font-weight:bold; font-size:11pt; text-align:center; ">Site 2</td><td style="background-color:#c2d69a; font-weight:bold; font-size:11pt; text-align:center; ">Site 3</td><td style="background-color:#c2d69a; font-weight:bold; font-size:11pt; text-align:center; ">Site 4</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="font-size:11pt; ">Worst Measure</td><td style="font-size:11pt; ">Measure 4</td><td style="font-size:11pt; ">Measure 1</td><td style="font-size:11pt; ">Measure 3</td><td style="font-size:11pt; ">Measure 5</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="font-size:11pt; ">2nd Worst Measure</td><td style="font-size:11pt; ">Measure 5</td><td style="font-size:11pt; ">Measure 4</td><td style="font-size:11pt; ">Measure 5</td><td style="font-size:11pt; ">Measure 4</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="font-size:11pt; ">3rd Worst Measure</td><td style="font-size:11pt; ">Measure 1</td><td style="font-size:11pt; ">Measure 2</td><td style="font-size:11pt; ">Measure 2</td><td style="font-size:11pt; ">Measure 1</td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td><td style="font-size:11pt; "> </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >G2</td><td >=COUNTIF(B$2:B$6,"<"&B2)+1+COUNTIF(B$1:B1,B2)</td></tr><tr><td >B9</td><td >=INDEX($A$2:$A$6,MATCH(SMALL(G$2:G$6,ROW()-(ROW($B$8))),G$2:G$6,0))</td></tr><tr><td >B14</td><td >=INDEX($A$2:$A$6,MATCH(LARGE(G$2:G$6,ROW()-ROW($B$13)),G$2:G$6,0))</td></tr></table></td></tr></table>

 

[BrAinZ]

Brilliant, I think that does it for me. THANK YOU!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.