High score list?

How do I create a high score list for an everlasting soccer tournament?

Perhaps something along these lines ..
(Only takes 5 mins to set-up <g>)

Assume the table below is
in Sheet1, cols A to C,
data from row2 down

Game# Scorer Goals
00001 ABC 1
00002 XYZ 3
00003 ABC 3
00004 XYZ 2

Put in E2:
=IF(B2="","",IF(COUNTIF($B$2:B2,B2)>1,"",ROW()))

Copy E2 down to say, E1000 to cover the max
expected number of rows of data in the table

Col E will flag and assign uniques in col B
with an arbitrary row number
(for us to extract the list of unique scorers in Sheet2)

(Hide away col E if desired)

In Sheet2
--------
List the headers in A1:B1 : Scorer, GoalsToDate

Put in A2:

=IF(ISERROR(SMALL(Sheet1!E:E,ROWS($A$1:A1))),"",INDEX(Sheet1!B:B,MATCH(SMALL
(Sheet1!E:E,ROWS($A$1:A1)),Sheet1!E:E,0)))

Put in B2:
=IF(A2="","",SUMIF(Sheet1!B:B,A2,Sheet1!C:C))

Put in C2: =IF(B2="","",B2-ROW()/10^10)

Select A2:C2, fill down by the same number of rows that was done in Sheet1
col E, viz.: to C1000

Col A will extract a unique list of all the scorers from Sheet1
Col B will total up the goals to-date for each scorer

Col C will act as an arbitrary tie-breaker* col
(to enable us to extract *all* the scorers in descending order in Sheet3,
irrespective of any ties in goals to-date)
*a frequent occurrence, especially in soccer <g>

(Hide away col C if desired)

For the sample data in Sheet1,
you'll get in Sheet2:

Scorer GoalsToDate
ABC 4
XYZ 5
etc

In Sheet3
-----------
List the headers in A1:B1 : TopScorers, GoalsToDate

Put in A2:

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

Copy across to B2, fill down to B1000

This will drive out the list of Top Scorers
in descending order

For the sample data in Sheet1,
you'll get in Sheet3:

TopScorers GoalsToDate
XYZ 5
ABC 4
etc

Top Scorers with identical goals to-date (ties)
will appear in the same relative order
that they are in Sheet2

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"nc-nc" <[email protected]> wrote in message
news:[email protected]...
> How do I create a high score list for an everlasting soccer tournament?