You need tiebreakers.
Here's one way using non-array formulas to drive it out ..
Illustrated in this sample:
http://www.flypicture.com/download/ODg1Mw==
Auto sort descending w tiebreakers.xls
Source data assumed in cols A and B, from row2 down.
Names in col A, scores in col B
In D2:
=IF(B2="","",B2-ROW()/10^10)
Leave D1 blank
In E2:
=IF(ROWS($1:1)COUNT($D:$D),"",INDEX(A:A,MATCH(LAR GE($D:$D,ROWS($1:1)),$D:$D,0)))
Select D2:F2, copy down to cover the max expected extent of data in col B.
Hide away col D. Cols E & F will return the required auto-sort of the names &
scores in descending order by scores. Names with tied scores, if any, will be
returned in the same relative order that they are listed in the source cols.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"soph" wrote:
Hi
I have an array of scores running down one column which correlate to staff
names in another column. I am looking to identify (by name) the top 2
scorers. This in itself seems easy enough except when the top 2 scores are
the same. I am using the Large formula to determine the top scores and can
then use a vlookup to retrieve the staff names however when the scores are
identical I keep ending up with the same name twice. I assume I need to
somehow reference the exact cells of the scores to give me different names
but nothing I have tried (Match/Index) has worked. Any help would be
fantastic
Cheers
Soph