Auto sort golf tournament spreadsheet

[timparker216]

I am trying to create a golf tournament leader board. We will be playing four rounds and I'd like to update and auto-sort the leaders after each round. I've used excel throughout my college career, but am not as good as I thought as I completely forget how to accomplish this. Hopefully it is possible to complete!

Pretty much I want everything to stay in place throughout the entire row, and then auto sort the scores after I enter the information (in rows "f","h","j","l") from lowest to highest based on the data in column "D". I've attached a copy of my spreadsheet if I was not clear enough. Thanks a lot!

[leo73pk]

Dear Tim,

Please find attached the revised file, when you feed in point in your sheet, the sheet Poiint Table will sort the teams w.r.t Total Net Point automatically.


Regards,

LEO73PK,


If someone was able to help with your question, please click on the icon at the top of their post that looks like the Scales of Justice

If your problem is solved, please update your post, otherwis

[Marcol]

If you are happy to have a macro based solution this might help.

Sheet1 will auto sort when all scores are returned in any round.

Sheet2 will auto sort as scores are returned in any round, in any order.

You can rename the sheets to suit your needs, it will not affect the code.

[leo73pk]

@ Marcol,

I have reviewed your solution you wrote a quite comprehensive macro for the golf sheet, i also used a macro to do so if convenient for you would you see that file which i enclosed and have a look at macro and comment if it has any limitation as far as i felt it is doing the job. But for my understanding if you appreciate & comment.

I hope your response would also be beneficial for timparker to understand the difference between two solutions available with him.

Regards,

[Marcol]

@ leo73pk
Your workbook doesn't appear to have a macro in it, that's why I posted a macro alternative I assumed your solution was formula based.

You have posted a .xlsx workbook, that cannot contain macros, and will delete any you may have used, when saved.
You should have recieved a warning to that effect.

Did you mean to save your workbook as a .xlsm file?

[leo73pk]

@ Marcol,

Accept my apologies, there was some error in saving file i'm just enclosing the file with MACRO, kindly review as its also sorting as soon as the scores are updated in the sheet!1,

and advice, if any, limitation in this macro.

[Marcol]

@ leo73pk

The main difference in the two approaches are the number of sheets involved, it's really up to Tim which way he wants to go.

You have only allowed for the situation where all the scores are different, my solutions sort the players alphabetically by surname when the scores are tied.
On reflection this might benefit by another sort key for "Handicap", again only Tim can tell us the rule if this is the case.

Your code will run on 2003 and above, mine is for 2007 and above, this may or may not be a problem, depends if the sheet is being distributed to others.

[leo73pk]

@ Marcol,

Thanks for the detailed reply in an expeditious manner.

In fact my macro is with the consideration of ranking on two parameters. In case the Net Points are same for two or more players then the next criteria will be Gross Points which automatically covers the impact of his column Handicap. (But you are right we both are not familiar with this Golf Scoring i.e. Handicap).

So lets wait for Tim's reply to further explain his requirement & the field "Handicap".

Thanks again.

[outofthehat]

To do it without macros, see the attached.

I've added 2 columns on the left-hand side of your input sheet (sheet1). First column uses the RANK() function to rank by score, and adds 100th of the row number to ensure there are no ties. (works for 98 players as set up - if you want it to work for 998, just divide row by 1000 rather than 100), and second column uses RANK() to give a unique position number.

On the Standings sheet you'll see a table that uses the Rank column on Sheet1 with VLOOKUP to display the players in ranks order (first row looks up rank 1, second looks up rank 2, etc). I've set it up so that where there's no data it displays "-", using IFERROR().

Now all you have to do is type in the scores on Sheet1, then they'll be ordered on the Standings sheet.

[timparker216]

Hey thanks everyone this is definitely what I was looking for, thanks for all the options!

[Colm_McCaffery]

Hi #outoftheheat, or anyone else as this is an old thread
is there any simple help in "reversing the order

I would like to use this sheet which is excellent, for stapleford scoring but cant quite understand the logic to the ordering (formula interpretation) and hence is someone gets a score that would go into round 1 of 38 then they would be ranked higher than 36 or 27 etc

[scottjoey]

Please see the attached spreadsheet that I have created.
I need to be able to create a team results tab that will combine round 1 and 2 for a total gross score and then list the top 4 boys and top 2 girls from each school.
I would also like to have an individual gross score tab that would give me an overall leaderboard for boys and girls after day 1 and day 2.
Any help you could give would be awesome.
Thanks.
Joey

[FDibbins]

scottjoey welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.