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

#### BrAinZ

##### New Member
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...

 Site 1 Site 2 Site 3 Site 4 Measure 1 2 3 1 4 Measure 2 1 2 3 3 Measure 3 4 1 4 1 Measure 4 3 2 1 4 Measure 5 2 1 3 4 Site 1 Site 2 Site 3 Site 4 Best Measure Measure 2 2nd Best Measure Measure 1 3rd Best Measure Measure 5 Site 1 Site 2 Site 3 Site 4 Worst Measure 2nd Wort Measure 3rd Worst Measure

<tbody>
</tbody>

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### DanteAmor

##### Well-known Member
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

##### New Member
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

##### Well-known Member
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

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