Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Handyy
 
Posts: n/a
Default Finding a name with biggest number


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

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Govind
 
Posts: n/a
Default Finding a name with biggest number

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?


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default Finding a name with biggest number

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

Biff

"Handyy" wrote in
message ...

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



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default Finding a name with biggest number

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"&R OW(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" wrote in
message ...

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



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default Finding a name with biggest number

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
---




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Handyy
 
Posts: n/a
Default Finding a name with biggest number


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

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default Finding a name with biggest number

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" wrote in
message ...

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



  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Handyy
 
Posts: n/a
Default Finding a name with biggest number


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

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default Finding a name with biggest number

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" wrote in
message ...

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



  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default Finding a name with biggest number

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
---




  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Handyy
 
Posts: n/a
Default Finding a name with biggest number


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


--
Handyy
------------------------------------------------------------------------
Handyy's Profile: http://www.excelforum.com/member.php...o&userid=30958
View this thread: http://www.excelforum.com/showthread...hreadid=507942

  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default Finding a name with biggest number

You're welcome !
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Handyy" wrote in
message ...

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
finding the right number kevindict Excel Worksheet Functions 2 September 7th 05 11:27 PM
Number of labels on X-axis one more than number of values on Y-axi Gudrun Charts and Charting in Excel 5 August 26th 05 01:55 PM
Need number of Saturdays and number of Sundays between 2 dates Class316 Excel Worksheet Functions 1 June 10th 05 02:47 AM
Finding a number in a table? JoeBed Excel Discussion (Misc queries) 10 June 4th 05 11:52 PM
finding row number? james Excel Discussion (Misc queries) 3 February 3rd 05 12:56 AM


All times are GMT +1. The time now is 08:01 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"