I'm new and really don't know how to word my title or this question properly. I run a golf league and I have a spreadsheet that keeps track of each players points earned as well as their stroke average. I'd like those values to auto populate and be tied to their name in separate sortable lists (one for points and one for stroke average) that don't get mixed up when I sort by the number values. I've attached the spreadsheet here.
1. you dont really say how you want them sorted...I sorted by stroke average
2. I see a few repeat/duplicate scores, the simplest way to get around that is to use a helper column (that can be hidden) to create a tie break. I used a simple 1st-come-1st-served option, so eg, the 1st 1 44 score came 1st, the 2nd 22 came 2nd etc. If you want a different approach, let me know.
3. Not really sure where you came up with the answers in AK, did you just enter random values?
OK to my suggestion (see the attached file). I only worked out the top table for you, see if you can do the rest (if not, shout)...
1. I added a helper in AD and used this (it creates the tie breaker)...
AD7=IF(AB7<>"stroke avg.","",AC7+COUNTIF($AC$7:$AC7,AC7)/100)
copied down
2. I added a count in AE...1-18
3. for the names...
AI7=INDEX(B:B,MATCH(AJ7,AD:AD,0))
copied down
For the POINTS (all the rest is based on this)...
AJ7=LARGE(IF($AB$7:$AB$60="stroke avg.",$AD$7:$AD$60),AE7)
This is an ARRAY formula
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Then copy that down.
Let me know if this is what you want, and whether you can duplicate this for the rest of your requirements?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Thanks for your help FDibbins! This is all way over my head but what you did works beautifully with the sorting! The horizontal rows are golf scores for 9 holes so it is very common to have duplicates, so we don't need a tie breaker there. Ties in the point standings are OK as we use our own manual system to break ties at the end of the season. I have attached a PDF of our spreadsheet from last season marked up in red. The upper right area is for the points standings (not stroke average as you currently have it) and the lower right area is for the stroke average (which I'd like to sort the same way as you did in the other area above).
Why a PDF? Please provide an Excel workbook.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Provided PDF redlined to show what values go where on the sheet.
TeeBone, just up the road from me (Altoona)
I needed to add in a tie breaker, otherwise the 1st name with that score would be used for all occurrences of that score.
I have done the 1st 2 sets for you (points and stroke avg). See if you can adapt that for the 2nd table?
Correct, never been to Altoona but my 10yr old grandson is a HUGE Steelers fan so we're looking to get out to a game this season.
The stroke average table looks great except I need that one sorted from low to high. I was able to change that by switching LARGE to SMALL in the formula...that was the easy one. Took me quite a while but I was able to do the 2nd half of each of the charts.
Thanks soooo much for your help!!
Great stuff, Im happy it helped you
Hey FDibbins...I have another problem I need a solution for, you up for a challenge?
Sure, but please start a new thread for that.
Will do...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks