Finding a name with biggest number

  • Thread starter Thread starter Handyy
  • Start date Start date
H

Handyy

Hi, I've got another problem which I can't figure out :

I have a hockey scoring sheet, which has player names, goals and assis
with it. What I want to do, is to print in to a different sheet player'
name and goal amount, who has scored the most goals

Example of a scoring sheet

Code
-------------------
----A---- ----B---- ----C---

Player Goals Assist

Jagr 33 43
Staal 33 3
Alfredsson 32 3
Kovalchuk *35* 3
Heatley 32 3
Ovechkin 34 3
-------------------

The generated result I'm looking for would look like this

Code
-------------------
----A---- ----B---- ----C---

Player Goals Assist

Kovalchuk 35 3
-------------------

I know this sounds silly, but what I need is more complicated than thi
and I want to search the best scorer overall from different resul
sheets. I want to do daily/monthly/ect statistics so Pivot Point syste
isn't useful (Or at least I think so). The generated sheet should alway
change if someone else has scored more goals

Is this possible to do with a normal excel function
 
Hi,

Lets say your source data is in Sheet 2 andin the range A1:C8, use this
formula in Sheet 1 to get the details of the highest scorer:

in Cell A1, to get the player name =
=OFFSET(Sheet2!A1,MATCH(LARGE(Sheet2!$B$1:$B$8,1),Sheet2!$B$1:$B$8,0)-1,0)

and copy it across to cell B1 and C1 to get the Goals and Assists.

Regards

Govind.
 
Here's a non-array formulas play which draws from source data in 2 separate,
identically structured sheets, and ultimately provides an auto full
descending sort of all players by Goals (using an arb tiebreaker)

A sample construct is available at:
http://www.savefile.com/files/4087988
Full Descending Sort with TieBreaks From 2 Sheets.xls

Assume 2 source tables in sheets named: X and Y
data in cols A to C, from row2 down to row11 (say)
In A1:C1 are col headers: Player, Goals, Assists
(players listed in each sheet are assumed unique)

In a new sheet: Z
List the sheetnames down in D2:D3 : X, Y

Put in E2:
=IF(INDIRECT("'"&INDEX($D:$D,COLUMN(A1)+1)&"'!A"&ROW(2:2))="","",ROW())

Copy E2 to F2, then fill down by as many rows as required to cover
the max expected extents in X and Y. As the max expected data in X and Y is
10 rows each, fill down to F11.

Paste the same col headers in A1:C1, viz.:
Player, Goals, Assists

Put in A2:
=IF(ISERROR(SMALL($E:$E,ROW(E1))),
IF(ISERROR(SMALL($F:$F,ROW(E1)-COUNT($E:$E))),"",
INDEX(Y!A:A,MATCH(SMALL($F:$F,ROW(E1)-COUNT($E:$E)),$F:$F,0))),
INDEX(X!A:A,MATCH(SMALL($E:$E,ROW(E1)),$E:$E,0)))

Copy A2 to C2, then fill down to cover the *total* extent of data in X and
Y, i.e. in this case, filled down by 10 rows per sheet x 2 sheets = 20 rows,
to C21

Z auto-returns a combined stacked listing of data from the source tables in
X and Y (data from X stacked above Y)

Then, in a sheet: Rank (say)
Paste the same col headers in A1:C1, viz.:
Player, Goals, Assists

Put in A2:
=IF(ISERROR(LARGE($D:$D,ROW(A1))),"",
INDEX(Z!A:A,MATCH(LARGE($D:$D,ROW(A1)),$D:$D,0)))
Copy A2 to C2

Put in D2: =IF(Z!B2="","",Z!B2-ROW()/10^10)
(Leave D1 empty)

(Col D is the arb tie-breaker col)

Select A2:D2, fill down to C21
(cover the same extent as the full list in Z)

The above will auto-return a full descending sort of all the players by the
Goals col. Players with tied goals, if any, will appear in the same
relative order that they appear in the combined list in Z.

---
 
Just magnificiant replies in this forum for problems, I can't thank you
experts enough!
Hi,

in Cell A1, to get the player name =
=OFFSET(Sheet2!A1,MATCH(LARGE(Sheet2!$B$1:$B$8,1),Sheet2!$B$1:$B$8,0)-1,0)

and copy it across to cell B1 and C1 to get the Goals and Assists.
Click to expand...

This seems useful for my purposes but the problem occurs when there is
more than one player who has scored same amount of goals. Can it be
altered somehow, that if there is several players with same goal
amount, it would print "x players tied with x goals"? That would come
in good use, if it's possible to do easily.
Here's a non-array formulas play which draws from source data in 2
separate, identically structured sheets, and ultimately provides an
auto full descending sort of all players by Goals (using an arb
tiebreaker)
Click to expand...

Thank you very much for this great effort, I'm not much an Excel expert
so I have to study that more before I learn to do that myself. I
downloaded your example file and it looks excellent for statistical
purposes!
 
You're welcome !
... but the problem occurs when there is
more than one player who has scored same amount of goals.
Click to expand...

The earlier sample provided already takes care of ties, but is more involved
because there were 2 separate source lists.

Here's a revised "simpler" example (assumes only a single source sheet),
which focuses on just the auto-extracting of the full descending list by
goals
(ties are catered for) in another sheet:
http://cjoint.com/?cdxwPg1vTo
ExtractDescendingSortedList_Handyy_wks.xls

---
 
I'm having difficulties with that simplier sorting worksheet. It works
great when I do exactly like you did, but when I try to insert it into
my own sheet, it doesn't work anymore.

I tried to figure out why it doesn't work, and it seems that it needs
to start from the first row of the sheet. I can't really understand why
is that and is there a way to go around this problem? Since I would need
the descending sorted results in the middle of a sheet, I just can't get
it work.

Here's a link to an example of the problem:
http://www.kolumbus.fi/handyy/misc/problem.xls
 
For each "set", we need to reset the top left anchor cell's row incrementer
part of the formula back to point to row #1, i.e. use ROW(A1)** in the
anchor cell's formula. Also, we could omit referencing the header row
(row11) for neatness, so just use instead in the 2nd set's anchor cell G2:

=IF(ISERROR(LARGE($K$12:$K$16,ROW(A1))),"",
INDEX(A$12:A$16,MATCH(LARGE($K$12:$K$16,ROW(A1)),$K$12:$K$16,0)))

Then copy G2 to J2, fill down

(No change is required to the criteria formula as filled in K12:K16)

**Using ROW(A1) is just a "std" practice. We could also use ROW(B1) or
ROW(G1) instead, all will evaluate to the same result.

Here's your sample, with the corrected formulas implemented:
http://cjoint.com/?cghRlF0E1u
Handyy_wks_problem.xls
 
Sorry, correction to typos in lines:
... so just use instead in the 2nd set's anchor cell G2: ....
Then copy G2 to J2, fill down
Click to expand...

Should read as:
... so just use instead in the 2nd set's anchor cell G12: ....
Then copy G12 to J12, fill down
Click to expand...


---
 
Back
Top