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

BrAinZ

New Member
Joined
Feb 14, 2010
Messages
26
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 1Site 2Site 3Site 4
Measure 12314
Measure 21233
Measure 34141
Measure 43214
Measure 52134
Site 1Site 2Site 3Site 4
Best MeasureMeasure 2
2nd Best MeasureMeasure 1
3rd Best MeasureMeasure 5
Site 1Site 2Site 3Site 4
Worst Measure
2nd Wort Measure
3rd Worst Measure

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,251
Office Version
2007
Platform
Windows
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
Joined
Feb 14, 2010
Messages
26
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
Joined
Dec 3, 2018
Messages
10,251
Office Version
2007
Platform
Windows
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>
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,251
Office Version
2007
Platform
Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,248
Messages
5,413,297
Members
403,472
Latest member
ArtisticOwl

This Week's Hot Topics

Top