# Thread: Trying to find worst and best measures from a list containing duplicates Thanks:  1 Post #5280258 (1) Likes:  1 Post #5280258 (1)

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

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

2. ## Re: Trying to find worst and best measures from a list containing duplicates

The formulas go

 A B C D E 1 Site 1 Site 2 Site 3 Site 4 2 Measure 1 2 3 1 4 3 Measure 2 1 2 3 3 4 Measure 3 4 1 4 1 5 Measure 4 3 2 1 4 6 Measure 5 2 1 3 4 7 8 Site 1 Site 2 Site 3 Site 4 9 Best Measure 1 10 2nd Best Measure 2 11 3rd Best Measure 2 12 13 Site 1 Site 2 Site 3 Site 4 14 Best Measure 4 15 2nd Best Measure 3 16 3rd Best Measure 2

Formeln der Tabelle
 Zelle Formel B9 =SMALL(B\$2:B\$6,1) B10 =SMALL(B\$2:B\$6,2) B11 =SMALL(B\$2:B\$6,3) B14 =LARGE(B\$2:B\$6,1) B15 =LARGE(B\$2:B\$6,2) B16 =LARGE(B\$2:B\$6,3)

3. ## Re: Trying to find worst and best measures from a list containing duplicates

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

4. ## Re: Trying to find worst and best measures from a list containing duplicates

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.

 A B C D E F G H I J 1 Site 1 Site 2 Site 3 Site 4 Site 1 Site 2 Site 3 Site 4 2 Measure 1 2 3 1 4 3 5 1 3 3 Measure 2 1 2 3 3 1 3 3 2 4 Measure 3 1 1 4 1 2 1 5 1 5 Measure 4 3 2 1 4 5 4 2 4 6 Measure 5 2 1 3 4 4 2 4 5 7 8 Site 1 Site 2 Site 3 Site 4 9 Best Measure Measure 2 Measure 3 Measure 1 Measure 3 10 2nd Best Measure Measure 3 Measure 5 Measure 4 Measure 2 11 3rd Best Measure Measure 1 Measure 2 Measure 2 Measure 1 12 13 Site 1 Site 2 Site 3 Site 4 14 Worst Measure Measure 4 Measure 1 Measure 3 Measure 5 15 2nd Worst Measure Measure 5 Measure 4 Measure 5 Measure 4 16 3rd Worst Measure Measure 1 Measure 2 Measure 2 Measure 1

 Cell Formula G2 =COUNTIF(B\$2:B\$6,"<"&B2)+1+COUNTIF(B\$1:B1,B2) B9 =INDEX(\$A\$2:\$A\$6,MATCH(SMALL(G\$2:G\$6,ROW()-(ROW(\$B\$8))),G\$2:G\$6,0)) B14 =INDEX(\$A\$2:\$A\$6,MATCH(LARGE(G\$2:G\$6,ROW()-ROW(\$B\$13)),G\$2:G\$6,0))

5. ## Re: Trying to find worst and best measures from a list containing duplicates

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