Golf Pro Database

[LouLBI]

I can download into spreadsheets such things as Point Standings, Money Leaders, World Ranking Driving Distance etc. with the listing of the golfers names in the order of their ranking.  What I would like to do is ask the question, for example,
what is Tiger Woods statistics in all of these categories.  Ending up with Tiger Woods, his Point Standing, His earned Money
His World Record his Driving Distance etc.  What is the best way of going about this?
Loulbi

 

[SkipVought]

Hi,

Those are the sorts of questions that can be easily answered using standard spreadsheet aggregation formulas like SUMIF(), COUNTIF() or SUMPRODUCT(), assuming that the golfer is the only criteria -- no VBA required. That sort of question is best addressed in forum68.

Please post a sample of your data or at least the relevant fields in your source table

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

If you have seperate worksheets for each element with names in colA and values in colB

Assume each is sorted by value then you can simply use the MATCH formula to return the value you require

lets assume names only in sheet1, point standings in sheet2

if tiger Woods is in Sheet1!A2 then

=match(A2,sheet2!A:A,0)

will return his ranking

you may need to add a -1 to the result if you have headers in row 1

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[LouLBI]

Thanks to you both for your help. I will post a workbook file as you requested, but I don't know how. Not as simple, apparently, as attaching a file in email.
LouLBI

 

[SkipVought]

Regardless if you post a link to your workbook, many of us are restricted from downloading data, by company security.

Please post sample data (copy 'n' paste from your sheet) right here!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LouLBI]

This Rank Player Events Avg Points Total Points Points lost Points gained Country
Week Last
Week
1 1 Luke Donald 52 10.29 534.83 -197.68 191.05 Eng
2 2 Rory McIlroy 50 9.01 450.67 -146.59 208.67 Nir
3 3 Lee Westwood 47 8.16 383.68 -144.74 165.6 Eng
4 4 Tiger Woods 40 6.71 268.44 -56.06 180.82 USA
5 5 Bubba Watson 47 6.21 291.71 -81.82 189.23 USA
6 6 Matt Kuchar 51 5.97 304.59 -108.59 158.79 USA
7 7 Justin Rose 52 5.67 295.08 -88.91 176.55 Eng
8 8 Hunter Mahan 52 5.41 281.08 -86.86 164.84 USA
This is a portion of one of the worksheets from the workbook.
Lou

 

[xlbo]

If all stats are in 1 sheet then you will need to use the RANK() function on each one you are interested in and vlookup or index/match to return the ranked value against the name

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[LouLBI]

There are many sheets, and I would like to access the stats in all the other sheets for a particular golfer. Say I want to know how Tiger Woods did in Drives, Money making, Ranking, etc. that is in different sheets, then compile all of it as Tiger Woods Data Base.

 

[SkipVought]

...in different sheets, then compile all of it as Tiger Woods Data Base

It apppears that you currently have mess ("in different sheets"), and now you want to complicate your mess ("compile all of it as Tiger Woods Data Base")!

Ideally, you would have NORMALIZED data in a single table (sheet) or a system of tabes (sheets) where each table is one relation set. From such a structure, you could very simply QUERY your data to obtain stats for ANY player, in a metter of seconds without the need for VBA, or use a simple aggregation or lookup function.

I would put my efforts into 1) discovering how to construct a proper normalized table and 2) reorganizing your data as such. If you pursue an Excel solution, you might check out...

faq68-5184


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[LouLBI]

I'm afraid that since I was concerned about using too much space, I did't make myself clear. At the risk of taking up too much room, I will try again.
There are well over 100 golfers in this data base, but not all golfers are in each category, so the number of golfers in each category varies. There are 9 categories.
Here is a sample of three of the categories: Unfortunately, when I copy and paste and preview the posting, the data does not show up in columns, so it may be a bit confusing.

2012 Regular Season FedExCup Points Standings
Rank Leader Events Points Back
1 Jason Dufner 14
2 Hunter Mahan 12 258
3 Tiger Woods 9 331
4 Zach Johnson 14 349
5 Bubba Watson 10 363
TOUR Average 12 332
Sheet1


Money Leaders
Rank Leader Events Total
1 Jason Dufner 14 $3,800,172
2 Hunter Mahan 12 $3,211,068
3 Bubba Watson 10 $3,204,778
4 Rory McIlroy 8 $3,164,700
5 Matt Kuchar 12 $3,082,409
TOUR Average 12 $620,219
Sheet2

Official World Golf Ranking
Rank Leader Events Total
1 Luke Donald 52 10.29
2 Rory McIlroy 50 9.01
3 Lee Westwood 47 8.16
4 Tiger Woods 40 6.71
5 Bubba Watson 47 6.21
TOUR Average 44
Sheet3

I would like to create a document that has every golfer with his Point Standings, Money Earned, World Ranking and the other six categories. Some of the categories will be blank.
I apologize in advance if I have been too verbose.
LouLBI

 

[LouLBI]

If someone can show me one simple thing, I would appreciate it. If I go to a spreadsheet in a work book and select a cell, how do I find the cell in other spreadsheets that have the same content.
Thank you in advance.
Lou

 

[PHV]

Have a look at the MATCH worksheet function
or here: faq68-5829

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[xlbo]

Have a look at the MATCH worksheet function

which is what I suggested a week ago!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question