software

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: www.excelforum.com/member.php...oamp;userid=30958
View this thread: www.excelforum.com/showthread...hreadid=507942Hi,

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

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

Biff

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

Put in E2:
=IF(INDIRECT(quot;'quot;amp;INDEX($D:$D,COLUMN(A1) 1)amp;quot;'!Aquot;amp;R OW(2:2))=quot;quot;,quot;quot;,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))),quot;quot;,
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))),quot;quot;,
INDEX(Z!A:A,MATCH(LARGE($D:$D,ROW(A1)),$D:$D,0)))
Copy A2 to C2

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

(Col D is the arb tie-breaker col)

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

Should read as:
gt; Select A22, fill down to D21
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---

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

Govind Wrote:
gt; Hi,
gt;
gt; in Cell A1, to get the player name =
gt; =OFFSET(Sheet2!A1,MATCH(LARGE(Sheet2!$B$1:$B$8,1), Sheet2!$B$1:$B$8,0)-1,0)
gt;
gt; 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 quot;x players tied with x goalsquot;? That would come
in good use, if it's possible to do easily.

Max Wrote:
gt; Here's a non-array formulas play which draws from source data in 2
gt; separate, identically structured sheets, and ultimately provides an
gt; auto full descending sort of all players by Goals (using an arb
gt; 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: www.excelforum.com/member.php...oamp;userid=30958
View this thread: www.excelforum.com/showthread...hreadid=507942You're welcome !

gt; ... but the problem occurs when there is
gt; 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 quot;simplerquot; 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:
cjoint.com/?cdxwPg1vTo
ExtractDescendingSortedList_Handyy_wks.xls
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;Handyyquot; gt; wrote in
message ...
gt;
gt; Just magnificiant replies in this forum for problems, I can't thank you
gt; experts enough!
gt;
gt; Govind Wrote:
gt; gt; Hi,
gt; gt;
gt; gt; in Cell A1, to get the player name =
gt; gt;
=OFFSET(Sheet2!A1,MATCH(LARGE(Sheet2!$B$1:$B$8,1), Sheet2!$B$1:$B$8,0)-1,0)
gt; gt;
gt; gt; and copy it across to cell B1 and C1 to get the Goals and Assists.
gt;
gt; This seems useful for my purposes but the problem occurs when there is
gt; more than one player who has scored same amount of goals. Can it be
gt; altered somehow, that if there is several players with same goal
gt; amount, it would print quot;x players tied with x goalsquot;? That would come
gt; in good use, if it's possible to do easily.
gt;
gt; Max Wrote:
gt; gt; Here's a non-array formulas play which draws from source data in 2
gt; gt; separate, identically structured sheets, and ultimately provides an
gt; gt; auto full descending sort of all players by Goals (using an arb
gt; gt; tiebreaker)
gt;
gt; Thank you very much for this great effort, I'm not much an Excel expert
gt; so I have to study that more before I learn to do that myself. I
gt; downloaded your example file and it looks excellent for statistical
gt; purposes!
gt;
gt;
gt; --
gt; Handyy
gt; ------------------------------------------------------------------------
gt; Handyy's Profile:
www.excelforum.com/member.php...oamp;userid=30958
gt; View this thread: www.excelforum.com/showthread...hreadid=507942
gt;

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:
www.kolumbus.fi/handyy/misc/problem.xls--
Handyy
------------------------------------------------------------------------
Handyy's Profile: www.excelforum.com/member.php...oamp;userid=30958
View this thread: www.excelforum.com/showthread...hreadid=507942For each quot;setquot;, 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))),quot;quot;,
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 quot;stdquot; 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:
cjoint.com/?cghRlF0E1u
Handyy_wks_problem.xls

--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;Handyyquot; gt; wrote in
message ...
gt;
gt; I'm having difficulties with that simplier sorting worksheet. It works
gt; great when I do exactly like you did, but when I try to insert it into
gt; my own sheet, it doesn't work anymore.
gt;
gt; I tried to figure out why it doesn't work, and it seems that it needs
gt; to start from the first row of the sheet. I can't really understand why
gt; is that and is there a way to go around this problem? Since I would need
gt; the descending sorted results in the middle of a sheet, I just can't get
gt; it work.
gt;
gt; Here's a link to an example of the problem:
gt; www.kolumbus.fi/handyy/misc/problem.xls
gt;
gt;
gt; --
gt; Handyy
gt; ------------------------------------------------------------------------
gt; Handyy's Profile:
www.excelforum.com/member.php...oamp;userid=30958
gt; View this thread: www.excelforum.com/showthread...hreadid=507942
gt;
Sorry, correction to typos in lines:
gt; ... so just use instead in the 2nd set's anchor cell G2:
....
gt; Then copy G2 to J2, fill down

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

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