Home |
Search |
Today's Posts |
#1
|
|||
|
|||
High score list?
How do I create a high score list for an everlasting soccer tournament?
|
#2
|
|||
|
|||
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))),"",IN DEX(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 <atyahoo<dotcom ---- "nc-nc" wrote in message ... How do I create a high score list for an everlasting soccer tournament? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto scroll down data validation list | Excel Discussion (Misc queries) | |||
Extracting Values on one list and not another | Excel Discussion (Misc queries) | |||
How do I find out what items are in one list but not in another l. | Excel Discussion (Misc queries) | |||
Selecting data from a list based on entered values | Excel Discussion (Misc queries) | |||
Counting Repeated text or duplicates in a list | Excel Discussion (Misc queries) |