Highest score in team

I have a team sheet listing players� names against their accumulated score.
I want to re-order this list, on a new sheet, such that the player with most
points is listed at the top, the player with the next most is under that one,
and so on. I don�t want to use �sort� as I want the players names to re-sort
as I key in their scores after each match.
Is there a worksheet function (or combination of functions) that will allow
me to do this? How will is sort if two players have the same score?

One play to try ..

Assuming source data is in Sheet1, cols A and B, data from row2 down (Names
in col A, Scores in col B)

Use an empty col to the right, say col C?

Put in C2: =IF(B2="","",B2-ROW()/10^10)
Copy C2 down to say, C100, to cover max expected data in col B
(can copy down ahead of expected data input)

Col C will function as an arbitrary tie-breaker col for the scores in col B

In Sheet2:

With the same col labels in A1:B1 (e.g.: Name, Scores)

Put in A2:

=IF(ISERROR(LARGE(Sheet1!$C:$C,ROWS($A$1:A1))),"",INDEX(Sheet1!A:A,MATCH(LAR
GE(Sheet1!$C:$C,ROWS($A$1:A1)),Sheet1!$C:$C,0)))

Copy A2 across to B2, fill down to B100
(cover the same range as in col C in Sheet1)

Cols A and B in Sheet2 will return the descending sort of the list in
Sheet1. Players with tied scores (if any) will appear in the same relative
order that they are in Sheet1

The sorted list in Sheet2 will auto-update based on subsequent inputs in
Sheet1's cols A and B (up to the max range covered by the formula in col C,
i.e. row100)

Adapt to suit ..
--
Rgds
Max
xl 97
---
GMT+8, 1� 22' N 103� 45' E
xdemechanik <at>yahoo<dot>com
----
"Hugh Murfitt" <[email protected]> wrote in message
news:[email protected]...
> I have a team sheet listing players' names against their accumulated
score.
> I want to re-order this list, on a new sheet, such that the player with
most
> points is listed at the top, the player with the next most is under that
one,
> and so on. I don't want to use "sort" as I want the players names to
re-sort
> as I key in their scores after each match.
> Is there a worksheet function (or combination of functions) that will
allow
> me to do this? How will is sort if two players have the same score?

On Mon, 13 Jun 2005 02:51:02 -0700, Hugh Murfitt
<[email protected]> wrote:

>I have a team sheet listing players� names against their accumulated score.
>I want to re-order this list, on a new sheet, such that the player with most
>points is listed at the top, the player with the next most is under that one,
>and so on. I don�t want to use �sort� as I want the players names to re-sort
>as I key in their scores after each match.
>Is there a worksheet function (or combination of functions) that will allow
>me to do this? How will is sort if two players have the same score?

I'm not sure how you have your sheet setup, so you may need to modify these
instructions. I will assume you have 22 players.

Set up your data entry sheet (Sheet1) as follows:

A1: Rank
B1: Player
C1: Total Score
D1: Date of first match
E1: Date of second match
F1: Date of third match
G1: ...

A2: =RANK(C2,C$2:C$23)+COUNTIF(C$2:C2,C2)-1
B2: Name of Player1
C2: =SUM(D2:IV2)
E2:IV2 match scores

Add the other players names to column B
Copy/Drag down the formulas in column A and column C to row 23

=====================
On Sheet2:

A1: =IF(Sheet1!A1="","",Sheet1!A1)
Copy/Drag to the right as far as needed.
Format the dates appropriately

A2: =RANK(C2,$C$2:$C$23)
copy/drag down to row 23

B2: =VLOOKUP(ROW()-1,Sheet1!$2:$23,COLUMN(),FALSE)
copy/drag down to row 23
then select B2:B23 and copy/drag across as far as needed.

============================

The RANK formula in Sheet1!A2 sets up a unique rank for entries that would
otherwise have an identical rank. The RANK formula on Sheet2 gives the actual
ranks of the players, without the duplicate adjustment.

Players with identical ranks will be listed, on Sheet2, in the order of their
ranking on Sheet1.

===========================

You may need to make some adjustments to prevent absent score entries on Sheet1
from showing up as zeros on Sheet2, but we'll need more info about your setup
to do that.

Good luck!


--ron

"Hugh Murfitt" wrote:

> I have a team sheet listing players’ names against their accumulated score.
> I want to re-order this list, on a new sheet, such that the player with most
> points is listed at the top, the player with the next most is under that one,
> and so on. I don’t want to use “sort” as I want the players names to re-sort
> as I key in their scores after each match.
> Is there a worksheet function (or combination of functions) that will allow
> me to do this? How will is sort if two players have the same score?

You're welcome, Hugh !
--
Rgds
Max
xl 97
---
GMT+8, 1� 22' N 103� 45' E
xdemechanik <at>yahoo<dot>com
----