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 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>
 

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>
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Macro to copy values across rows and transposing them and add the user id
    [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Hi,[/COLOR][/SIZE][/FONT] [FONT=Times New...
Top