• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to have Excel tabulate and return the custom results in a golf match format

Hi Narayan,


Yes, you got it working right after I have given your new formula a workout with several possiblities. I have copied and pasted to ONE tab and gave it a test there. Here is the uploaded file and you can check it:


https://www.dropbox.com/s/stste84hkc6fiod/MatchFormat_4.xlsx


So what have you decided to do with TWO tab? VBA and go for it or what? If you are still thinking, tnen I have one last task here.


In the DASHBD tab, the goal is to look at the list names of golfers on each team and write a formula for Excel to match the name of golfer in each round, then assign the result; a win or loss or a tie for the performance record.


How should it work the best way? My thought is this: the name of golfer will be inputed in Column B of the ONE, TWO, and THREE tabs. A logical way may be inputting the name of golfers in alphabetical order and their HCP (in A:13:B38 for Team AZ and H13:I38 for Team CA) in DASHBD tab. The range in DASHBD (C13:F38 and J13:M38) would have the LOOKUP formula written to link to the range of game results (won, lost or tied) found under Column X and match to Column B in ONE, TWO, and THREE tabs for tabulation.


Mark
 
Hi Mark ,


Thanks for the feedback.


Regarding your requirement on the DASHBD tab , have you already done it manually any time ? If so , if you can upload the file , it'll make testing easier.


When is your next outing ?


Narayan
 
Hi Narayan,


Did you mean to say the manual tabulation already on DASHBD should accurately reflect what had resulted in the other workbooks? Almost. Nevertheless just go ahead to write the formula. Dont' worry. It shall be my responsibility for its accuracy after the formula is written up.


The outing for this match tournament is scheduled for April 12. We still have time for this and the workbook tab, TWO. I would feel better to finish it up this month just to be sure for field testing. :)


Mark
 
Hi Narayan,


That is cool (and so quickly) you came up with the IF() to compare the team pair inside the SIGN(). That really kissed away the VBA thought! Then take a look at how I tried to turn off the AS cells in cells X73:X76 as a test before copying to the rest of range in Column X for TWO tab. The SUM(X73:X76)=0 inside the IF() does not do the trick. What will? Check the file here to see the issue:


https://www.dropbox.com/s/7r4m2cffboziyvv/MatchFormat_5.xlsx


Mark
 
Hi Narayan,


Hey, my eyes were looking so hard at the Column W. The fix should have been in the rows of AS and 1UP with that same SUM() I wrote above. The file is updated as above and check that out.


If good, then we can move on to the DASHBD tab to writeup the LOOKUP() or whatever you come up so neatly.


Mark
 
Hi Narayan,


Do you find AND(C73=0,C74=0) advantageous over SUM($W73:$W74)=0 when both is working fine for my purpose, let me hear this out otherwise


Then are we ready for DASHBD, right?


https://www.dropbox.com/s/7r4m2cffboziyvv/MatchFormat_5.xlsx


Mark
 
Hi Narayan,


How is it coming for the LOOKUP (or whatever you come up with) project on DASHBD?


The time is getting close for field testing.


Thank you again!

Mark
 
Hi Mark ,


Can you check this version ? I have done the tabulation only for one team ; once you confirm that it's OK , we can cover the second team too.


https://www.dropbox.com/s/fe1fqox359xs8jm/MatchFormat_7.xlsx


Narayan
 
Hi Narayan,


You got it right.


How did you possibly get Excel to read and match when the DASHBD has only the last names and the other tabs shows both the first and last name of golfers.


The golfers' names are inputed in ONE, TWO, and THREE tabs, and thereby should we make the Column B and I as recipient cells in an alphabetical order or not?


Once you do for the second team, I will finish with a SUM() formula for the ROUNDS (Columns C & J) to add up the number of rounds played by a golfer.


Mark
 
Hi Mark ,


Can you check your file here ?


https://www.dropbox.com/s/tufvjloysdp2xuq/MatchFormat_8.xlsx


Narayan


P.S. I have introduced a new tab called Players ; however , you can see that the formulae do not extend the entire range , since this slows down the file ; you can try copying the formulae in row 2 downwards , and see if your system tolerates them.
 
Hi Narayan,


When you say "...your system...", did you mean my hardware or software version? Keep in mind this testing was made on my iMac, a late 2012 model with Intel Core i7 and a latest graphic card.


Here is how my test ran on your MatchFormat_8.xlsx version;


Started to copy down each column from Row 2 in PLAYERS tab. Done that. Then I noticed two golfers has first name then last name in ONE tab. Edited to match the last name then first name like the rest of golfers. No slowing effect there and those two edited names are surely updated across the range. So far so good.


I went ahead to add four new golfers names, two on each team in the first available "card" that is Group 8 in ONE tab. Upon hitting the RETURN after entering the first CA golfer, the Calculating note shows up on the bottom window bar slowing down the system for a couple of seconds. Then the next three golfers were added without any hint of slowing down. I went back to DASHBD to check if those names are updated there. I see blank cells there where list of golfers were before this editing. Hmmm.


I went ahead to fill out the make-believe strokes to see if with a complete scorecard, would the golfers' names come back and that is still negative. Does this means the formula needs fixing some more?


After your next comment and perhaps fix, I will try testing on my 5 year old Intel-based MacBook Pro.


Mark
 
Narayan,


I suppose you may like to see how I did with your MatchFormat_8 so here is my uploaded version 9 to give you an idea:


https://www.dropbox.com/s/dgrtp20z5bt1s2z/MatchFormat_9.xlsx


Mark
 
Hi Mark ,


The problem was the range PLAYERS!L2:L35 ; the list of players extended till row 37 , and hence the range PLAYERS!L2:L35 also needed to be changed to PLAYERS!L2:L37.


I have added a named range Team to take care of this ; you can check the file here :


https://www.dropbox.com/s/tb2645q92i98i8t/MatchFormat_9.xlsx


Narayan
 
Hi Narayan,


I see. The trouble is that in my version with the window too small, thus I cannot edit. Can you change the range to include the maximum 52 possible players to cover 26 golfers on each team.


I still do not see the 4 new golfers name show up in the Column B and I in DASHBD. Can I rename it LEADER by the way?


I went ahead to add another 4 new golfers names and the make-believe strokes in Group 9 in ONE tab. That still does not show up in the standings. Maybe you need to redefine the range again for me. I would seriously consider Excel 2011 for Mac still.


Here is the uploaded file:


https://www.dropbox.com/s/dgrtp20z5bt1s2z/MatchFormat_9.xlsx


After your fix, I will try it on my laptop. But it is way past bedtime. Will do it in the morning. Thank you!


Mark
 
Hi Narayan,


I ran this MatchFormat_10 on my MacBook Pro and did not notice any delay whatsoever (maybe just a tad - yet not bothersome at all) while filling the scorecard for Group 9 in TWO tab. Seems to look good at first glance. Then I noticed this new golfer named David Thompson who have declared winner twice in the DASHBD (cell I30) however take a look at ONE and TWO tabs where he lost both games.


What is the significance of cell O1 where the value shows 51 in the PLAYERS tab? P1 shows for list of entries a value of 41; shouldnt it be an even number - 40 in this case in the same tab?


See what I did in this uploaded file and fix the formula somewhere to invert the result:


https://www.dropbox.com/s/d0xu52pjo4rjcje/MatchFormat_10.xlsx


Will my editing the tab name to 'LEADER' affect the formulas anywhere in the workbooks?


Mark
 
Hi Mark ,


Can you check this version ?


https://www.dropbox.com/s/k4k2ti7v6uk8b4s/MatchFormat_10_1.xlsx


If you change the tab name , Excel should reflect the change in the named ranges ; there should be no problem.


Narayan
 
HI Narayan,


All looks good and ready for use this weekend. As I put on the finishing touch, I also copied as a group the two RESULTS and SKINS tabs in NetSkins.xlsx. Then next came the question how I want to keep the link or break the automatic links to information in other workbook. I had assume prehaps wrongly to break the link thinking the names defined (like your list manager) would port over to this filename. SInce I plan leave home for the event in about 12 hours, could we correct this. Either you straighten out the List Manager or tell me how to redo the move correctly if that is easier.


Here is the uploaded file so you can see the #REF! in the NetSkins tab:


https://www.dropbox.com/s/8zy9n5z9oc1t7c4/2013DCMatch_1.xlsx


Thank you in advance for ALL of your assistance. Wish me luck.


Mark
 
Hi Mark ,


I have absolutely no idea how to get back all the original formulae , where ever #REF! is being displayed now ; this can only happen if there has been a row / column delete , or a cut / copy / paste of relevant cells ; the only way to get everything back is to try and see at what stage this happened ; if you have a backup , restore that , and start redoing whatever was done from that stage onwards.


Secondly , please note that the formulae on the Players tab are very compute-intensive , and can slow down your system considerably ; the columns are as follows :


Column A : Complete List of Players

Column B : List of Players in column A less those cells which have the word GROUP in them

Column C : List of Players in column B less those cells which have 0 in them

Column D : List of Players in column C less duplicates


Now , if you introduce the sub-heading PAIR 1A , PAIR 1B , and so on in the THREE tab , you are introducing one more element which needs to be removed , which means more formulae are required. I suggest you use the words GROUP 1A , GROUP 1B and so on instead , so that the existing formulae in column B will remove these from the list.


Narayan
 
Hi Narayan,


Your suggestion to rename from PAIR to GROUP is duly noted. Made the change.


This time I backtracked my idea by keeping two files separate. No need copying the worksheets over to MatchFormat.xlsx. Instead I would open the NetSkins.xlsx and use the ='[filename]workbook' reference between two files. It works better this way...obviously less computing-intensive after all I only needed to accomplish that at end the second round. When I entered the individual strokes in MatchFormat filename and then open the NetSkins file, the strokes are automatically updated from the MatchFormat file. Agreed with the logic in my approach?


Mark
 
You must log in or register to reply here.
Back
Top