Finding a name with biggest number

[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 assist with it. What I want to do, is to print in to a different sheet player's name and goal amount, who has scored the most goals.

Example of a scoring sheet:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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

Is this possible to do with a normal excel function?

[Govind]

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.


Handyy wrote:
> Hi, I've got another problem which I can't figure out
>
> I have a hockey scoring sheet, which has player names, goals and assist
> with it. What I want to do, is to print in to a different sheet player's
> name and goal amount, who has scored the most goals.
>
> Example of a scoring sheet:
>
> Code:
> --------------------
> ----A---- ----B---- ----C----
>
> Player Goals Assists
>
> Jagr 33 43
> Staal 33 36
> Alfredsson 32 38
> Kovalchuk *35* 33
> Heatley 32 36
> Ovechkin 34 31
> --------------------
>
>
> The generated result I'm looking for would look like this:
>
> Code:
> --------------------
> ----A---- ----B---- ----C----
>
> Player Goals Assists
>
> Kovalchuk 35 33
> --------------------
>
>
> I know this sounds silly, but what I need is more complicated than this
> and I want to search the best scorer overall from different result
> sheets. I want to do daily/monthly/ect statistics so Pivot Point system
> isn't useful (Or at least I think so). The generated sheet should always
> change if someone else has scored more goals.
>
> Is this possible to do with a normal excel function?
>
>

[Biff]

What happens if more than one player has the most goals?

Biff

"Handyy" <[email protected]> wrote in
message news:[email protected]...
>
> Hi, I've got another problem which I can't figure out
>
> I have a hockey scoring sheet, which has player names, goals and assist
> with it. What I want to do, is to print in to a different sheet player's
> name and goal amount, who has scored the most goals.
>
> Example of a scoring sheet:
>
> Code:
> --------------------
> ----A---- ----B---- ----C----
>
> Player Goals Assists
>
> Jagr 33 43
> Staal 33 36
> Alfredsson 32 38
> Kovalchuk *35* 33
> Heatley 32 36
> Ovechkin 34 31
> --------------------
>
>
> The generated result I'm looking for would look like this:
>
> Code:
> --------------------
> ----A---- ----B---- ----C----
>
> Player Goals Assists
>
> Kovalchuk 35 33
> --------------------
>
>
> I know this sounds silly, but what I need is more complicated than this
> and I want to search the best scorer overall from different result
> sheets. I want to do daily/monthly/ect statistics so Pivot Point system
> isn't useful (Or at least I think so). The generated sheet should always
> change if someone else has scored more goals.
>
> Is this possible to do with a normal excel function?
>
>
> --
> Handyy
> ------------------------------------------------------------------------
> Handyy's Profile:
> http://www.excelforum.com/member.php...o&userid=30958
> View this thread: http://www.excelforum.com/showthread...hreadid=507942
>

[Max]

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Handyy" <[email protected]> wrote in
message news:[email protected]...
>
> Hi, I've got another problem which I can't figure out
>
> I have a hockey scoring sheet, which has player names, goals and assist
> with it. What I want to do, is to print in to a different sheet player's
> name and goal amount, who has scored the most goals.
>
> Example of a scoring sheet:
>
> Code:
> --------------------
> ----A---- ----B---- ----C----
>
> Player Goals Assists
>
> Jagr 33 43
> Staal 33 36
> Alfredsson 32 38
> Kovalchuk *35* 33
> Heatley 32 36
> Ovechkin 34 31
> --------------------
>
>
> The generated result I'm looking for would look like this:
>
> Code:
> --------------------
> ----A---- ----B---- ----C----
>
> Player Goals Assists
>
> Kovalchuk 35 33
> --------------------
>
>
> I know this sounds silly, but what I need is more complicated than this
> and I want to search the best scorer overall from different result
> sheets. I want to do daily/monthly/ect statistics so Pivot Point system
> isn't useful (Or at least I think so). The generated sheet should always
> change if someone else has scored more goals.
>
> Is this possible to do with a normal excel function?
>
>
> --
> Handyy
> ------------------------------------------------------------------------
> Handyy's Profile:
http://www.excelforum.com/member.php...o&userid=30958
> View this thread: http://www.excelforum.com/showthread...hreadid=507942
>

[Max]

Typo in line:
> Select A2:D2, fill down to C21

Should read as:
> Select A2:D2, fill down to D21
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[Handyy]

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.

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)

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!

[Max]

You're welcome !

> ... but the problem occurs when there is
> more than one player who has scored same amount of goals.

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Handyy" <[email protected]> wrote in
message news:[email protected]...
>
> Just magnificiant replies in this forum for problems, I can't thank you
> experts enough!
>
> Govind Wrote:
> > 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.
>
> 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.
>
> Max Wrote:
> > 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)
>
> 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!
>
>
> --
> Handyy
> ------------------------------------------------------------------------
> Handyy's Profile:
http://www.excelforum.com/member.php...o&userid=30958
> View this thread: http://www.excelforum.com/showthread...hreadid=507942
>

[Handyy]

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

[Max]

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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Handyy" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> Handyy
> ------------------------------------------------------------------------
> Handyy's Profile:
http://www.excelforum.com/member.php...o&userid=30958
> View this thread: http://www.excelforum.com/showthread...hreadid=507942
>

[Max]

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

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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[Handyy]

Excellent, it works now smoothly. Thanks for all the help and effort!

[Max]

You're welcome !
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Handyy" <[email protected]> wrote in
message news:[email protected]...
>
> Excellent, it works now smoothly. Thanks for all the help and effort!