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
