Top Golfer

[BrianNowell]

My Challenge
At my local Golf Club we have an internal social group. At the end of a day’s play the Pro Shop provides USB data of scores in count-back order.
I have developed a Workbook from a cluster of spreadsheets that my predecessor operated manually. The purpose of the Workbook is (and was) to prepare a report in grades as well as archiving data for other purposes.

On single play events the players with the four best (countback) scores, not being visitors, are awarded points from 4 down to 1. Across the year there are certain member events that have a qualification criterion applied.

With all of that in mind my issue focuses upon 3 worksheets. The first is where the USB data is recorded, the second is the sheet which holds the qualification data and the last is the report and that is not an issue as that is the destination of the result. After data has been processed something like this table is generated. Ignore the right column as that assigns how I report the grade profiles.

The second last column is the one of concern. Here the top 4 member scores are awarded points before grading profiling.

3110104706 M A 87 16 71 2 1
3110105333 G H 82 10 72 2 1
3110106651 B G 77 5 72 3
3110102906 D H 88 18 70 3 4
3110107446 G H 92 19 73 5 1
3110101115 M P 91 18 73 6
3110107568 N M 94 25 69 4 7
3110106782 A E 102 31 71 1 8
3110106320 G H 106 35 71 9

The question then arises, “Is the person with the most points eligible to be given the Club award of that day?

The Megaformula below addresses that.
=IF(VLOOKUP(INDEX(Day_Results!$AJ$1:$AO$9,MATCH(4,Day_Results!$AO$1:$AO$9,0),1),’Yr Scores’!A3:BS128,71)>5,INDEX(Day_Results!$AJ$1:$AO$9,MATCH(4,Day_Results!$AO$1:$AO$9,0),2),IF(VLOOKUP(INDEX($AJ$1:$AO$9,MATCH(3,$AO$1:$AO$9,0),1),’Yr Scores’!A3:BS128,71)>5,INDEX($AJ$1:$AO$9,MATCH(3,Day_Results!$AO$1:$AO$9,0),1),IF(VLOOKUP(INDEX(Day_Results!$AJ$1:$AO$9,MATCH(2,$AO$1:$AO$9,0),1),’Yr Scores’!A3:BS128,71)>5,INDEX(Day_Results!$AJ$1:$AO$9,MATCH(2,Day_Results!$AO$1:$AO$9,0),2),IF(VLOOKUP(INDEX(Day_Results!$AJ$1:$AO$9,MATCH(1,Day_Results!$AO$1:$AO$9,0),1),’Yr Scores’!A3:BS128,71)>5,INDEX(Day_Results!$AJ$1:$AO$9,MATCH(1,Day_Results!$AO$1:$AO$9,0),1)))))

The table above resides in the “Day_Results” Worksheet.
The “Yr Scores” Worksheet holds the qualification value in column 71; column 1 holds the Player ID Now, in the most important case that is 3110107568. My strategy is:
=IF(VLOOKUP(INDEX(Day_Results!$AJ$1:$AO$9,MATCH(4,Day_Results!$AO$1:$AO$9,0),1),’Yr Scores’!A3:BS128,71)>5,INDEX(Day_Results!$AJ$1:$AO$9,MATCH(4, Day_Results!$AO$1:$AO$9,0)),2)……

Firstly I VLOOKUP the value 4 in my array on the “Day_Results” Worksheet and address that value at “Yr Scores” in column 71. Should that return a “false” I proceed to him who has 3 points. I’m trusting that of 4 highest scores at least one of them has played greater than 5 games.

At this time that massive formula works as best I can assess. My question however is, can that be simplified?

[John Franco]

Hi Brian,

Let’s follow the MF building process.

I need to understand your MF fully. First, could you please fill this MF template:

Purpose: describes what the MF does, not how it does it (You can write it at the end. You can use the output as a foundation).
Assumptions: lists each external variable, control, open file, or other element accessed by the MF.
Criteria and conditions: what’s the base logic that needs to be fulfilled to get the output. For example: if X happens, then the output is Y or something else
Effects: lists each affected external variable, control, or file, and the effect it has (only if it is not obvious).
Inputs: specifies the inputs needed to get the output (specify the data types and each input’s purpose).
Output: explains the values returned by the MF (specify the data type).

Source: https://www.excelgurublueprints.com/members/formulas/master-of-megaformulas/lessons/other-megaformula-building-blueprints/

As I understand it:

Purpose: the Golf megaformula awards the players with 4 highest scores in the second last column
Assumptions: countback scores are in the second last column
Criteria and conditions: if score is ? then I do ?
Effects: lists each affected external variable, control, or file, and the effect it has (only if it is not obvious).
Inputs: scores table (range?), results table (range?)
Output: Top golfer ID.

Please correct and complete my assumptions.

And could you upload a sample workbook? Remove any sensitive data. I activated the attachment feature below.

Looking forward to your response.

Thanks.

• This reply was modified 5 years, 1 month ago by John Franco.
• This reply was modified 5 years, 1 month ago by John Franco.
• This reply was modified 5 years, 1 month ago by John Franco.

[BrianNowell]

I did not anticipate that my Excel pasted sheet would lose its format under HTML and that may have cause some misinterpretations of my intent.
The Workbook is attached is a very heavily edit of my 5Mb Workbook. As such it is likely that one of those sheets has references to other parts of that file.

Purpose: To identify the player with the best score who is eligible for the Club award.
Assumptions: 1. Members are awarded descending points from 4 to 1 according to their individual 4 best scores.
2. There exists a table of values to determine eligibility.
Criteria and conditions: If player score meets eligibility then publish name else repeat search.
Effects: The eligible person’s name will appear on the Result Sheet.
Inputs:
Scoring Data Table: Day_Results!AJ1:AP9
Eligibility Data: ‘Yr Scores’!A3:BS128 Column BS or #71
Output: Player’s Name

To ensure that you understand I will describe processes before this MF is required.
USB data is loaded into Columns Q:AA and macro processed. Therein is the raw data in correct/countdown order, ie, Col AA. Other processes occur before Cols AJ:AQ are compiled. During this process the points 4 to 1 are assigned against Club member scores. (We have 3 grades and so the values from 1 to 9 represent the three persons in each of those – ignore those values as they determined the values 4 to 1). At the completion of that process the Q:AA table is relocated to AJ:AQ where the data is reordered to be read by the Day Report sheet. In that process the values 4 to 1 are disorganised. For our purposes only ‘Day Report’!F4 needs to be addressed.

The formula that resides there has been updated as I discovered that the earlier one (above) had at least two page references omitted.

Now, how the MF works:
We seek to find the ID Number of the person who Matches Points 4. Find that ID and then check if Col 71 in ‘Yr Scores’ is greater than 5, if so return the Player’s name to ‘Day Report’!F4, if not continue to seek.

Attachments:

You must be logged in to view attached files.

[John Franco]

Hi Brian,

Thanks for uploading the workbook and for the extra explanation. Now I get it!

Here are my thoughts…

If you complicate the data source, your formula gets more complex as well.

Using a lookup function to lookup the value of the parent lookup is a symptom of this unnecessary complexity.

I encourage you to simplify the data source first. For example, put everything that belongs to scores on the score sheet. No mirror columns, minimize hidden columns, minimize the use of macros.

Here’s how the MF (on Day Report) might look like after this improvement.

=IF(VLOOKUP(D12,’Yr Scores’!$BY$6:$CA$14,3,0)=TRUE,CONCAT(INDEX(yr_scores,MATCH(D12,’Yr Scores’!$BY$5:$BY$14,0),2),” “,INDEX(yr_scores,MATCH(D12,’Yr Scores’!$BY$5:$BY$14,0),3)),”No Player”)

What I did?

On the ‘Yr Scores’ sheet, I added a Position column holding the 4 to 1 info. I also added an Eligible column (TRUE if col 71 >5) And I added a Final column that assess both columns.

Then, on the ‘Day Report’ sheet, I just added four cells for 1 to 4 and copied down the MF.

I attach the reviewed workbook for your analysis and comments.

• This reply was modified 5 years, 1 month ago by John Franco.

Attachments:

You must be logged in to view attached files.

[BrianNowell]

John,

Thank you for your analysis. I can follow exactly what you have done and understand what you are telling me.
When I inherited the original workbook things were relatively easy, formulae and macros were simple. Over time I had to address changing demands which has meant refinements in layouts, new worksheets and macros. As such I’ve chosen to reference the source of data to avoid issues (that mirror column, I think I did intend to use it because I may have been having some referencing issues, unsure).

As to the need for hidden columns and macro usage, I will not always be the person who operates this application. As such that person only needs to see what they need to see. My macros (and Worksheet Protection) hopefully insure something that is user-friendly.
Now my thoughts on your analysis, and a ‘confession’. I did not make it clear that Day_Report is actually a formatted sheet (loaded with formulae) to accept daily data printable at A4 size. As such I must have just one name to appear in Day_Report!F4 and my complex MF does that.

Now, I’m beginning to think that I might relocate your Day_Report!D12:E15 to somewhere to the right but then reference Column AC on Day_Results. Then introduce a formula into Day_Report!F4 that finds the person that my MF chooses.
I have another consideration.

While I might be making things easy for myself, at some point, if my group chooses to continue with this Workbook, and I am not there, they need to find someone who can understand the complete Workbook; at that point I have built a Manual and have a MacroList worksheet; I guess I’m like the Server Admin, trust that all users can foul the system, 😊; all workbook formulae are macro coded as a backup in case someone does actually foul a worksheet that should be protected – now I’m getting deep, paranoid?

I don’t need to overcomplicate what overall is a complex network. I have realised that each Worksheet needs to be dedicated to one task. (The cell labelled ‘20 Best’ heads columns which gleans data from elsewhere and so must go). On any one day the Workbook must dynamically accept visitors, add newly recognised Club Members (from the parent group) and respond to whichever game format and course (we have two) is chosen on the day; it is genuinely complex.

[John Franco]

I understand Brian.

To get the single output formula back…

=IF(VLOOKUP(4,’Yr Scores’!$BY$6:$CA$14,3,0)=TRUE,INDEX(yr_scores,MATCH(4,’Yr Scores’!$BY$5:$BY$14,0),4),IF(VLOOKUP(3,’Yr Scores’!$BY$6:$CA$14,3,0)=TRUE,INDEX(yr_scores,MATCH(3,’Yr Scores’!$BY$5:$BY$14,0),4),IF(VLOOKUP(2,’Yr Scores’!$BY$6:$CA$14,3,0)=TRUE,INDEX(yr_scores,MATCH(2,’Yr Scores’!$BY$5:$BY$14,0),4),IF(VLOOKUP(1,’Yr Scores’!$BY$6:$CA$14,3,0)=TRUE,INDEX(yr_scores,MATCH(1,’Yr Scores’!$BY$5:$BY$14,0),4)))))

I got rid of the CONCAT function by creating a merge column on the Yr Scores sheet (First name and Last name).

But you need to trap the error when the VLOOKUP doesn’t find any of the scores (1 – 4). I guess you get N/A when there is no player with one of those 1-4 values. Maybe IFNA?

And in my humble opinion, complexity is not scalable. You have inherited a messy workbook. Are you going to pass the same mess to your sucesor?

Simplify the source and you get every instance simplified as well. Keep a single data source. Even if you are managing the PGA tour, you have a single data repository as well. It is not easy to conceive everything as a single database but the gold and magic is on the db design.

If you need to create more than one table, don’t forget to follow sound db design principles like one-to-many and one-to-one relationships.

Working with related tables using formulas is really painful. I don’t recommend you go that route.

Going beyond the VLOOKUP functionality means you need different tools. I recommend you explore the use of PivotTables with PowerQuery. You can relate tables using that tool. And use formulas on the summarized tables (after pivoting).

Your project is a data game, not a formula neither a macro game.

• This reply was modified 5 years, 1 month ago by John Franco. Reason: adding extra commentary

Attachments:

You must be logged in to view attached files.

[BrianNowell]

Your project is a data game, not a formula neither a macro game.

I’m sorry but I don’t understand what you mean.

As to how messy this workbook is is one of subjectivity. Yes, I know aspects of it are but you cannot see or understand that; to give you some sense of the internal complexities I’ve posted a partial hierarchy of how data is distributed from just one input source – the other feeds back such that it only impacts the Yr_Scores and worksheets below as appropriate.

As to whoever might be my successor I am very mindful; I have in place certain advices. I can be very confident that within my current group there is no-one who is likely to be able to create or even edit an MF. Whatever I pass on therefore needs to be easily understood and modified by someone capable of understand VBA macros. Because the workbook is dynamic, it needs to respond to data according to different game formats, recognise visitors, and add new members from the parent club, I have a data base of parameters of which are set dynamically.

Within the last two weeks I have looked at PivotTables, never having been introduced to them before.

But you need to trap the error when the VLOOKUP doesn’t find any of the scores (1 – 4). I guess you get N/A when there is no player with one of those 1-4 values. Maybe IFNA?

Those “scores” 4-1 define for me the highest Club scorers (visitors dismissed). I very much doubt that across the period of 12 months (some 47 weeks of play that at least one of the 4 named would have met the “>5” criterion. I won’t dismiss that but …. should the odds defy me I’m going to have to seek another plan).

Our community is rural with a population of about 5000-6000. The nearest large centre c.30,000 is 45 mins away; yes there are tertiary campuses there, but …. woould they assist?

Attachments:

You must be logged in to view attached files.

[John Franco]

Hi Brian,

It is my humble opinion, I don’t know the messy part hidden but the visible part, which is a mirror. It is not your fault. It is not easy to clean and structure the data because it is produced (somewhere I guess) in a particular way.

I am sorry for the confusion. By ‘data game’ I mean you should have a consolidated database with one-to-one and one-to-many relationships. Following your data hierarchy, here are some scenarios to consider, can the ’20 best? and ‘Yr_scores’ tables be merged? can the ‘Day_Report? and ‘Day_results’ tables be merged? That’s my point. Focus on normalizing the data, data entry toward a consolidate db structure. You are working with Excel not access.

If you have such a db, then running a pivottable might brings the answers you need.

If you want to work with separate tables, then you will need an index field that links each table and power query to merge them all and run pivottable on top of that…

I highly recommend having a look at PTs.

[Author]

Posts