Golf Spreadsheet Score ordering help

[caioz1jp]

Hi, So I have been having fun putting together this spreadsheet for my outing. Right now I am having trouble with getting it to place people in order from lowest score to highest score. The issue is if there is a tie it will repeat whatever the first tie score is. How they get ordered is not relevant as if three people tie for first second and third we just combine the money and split it between the three and move to the next place. Attached is my spreadsheet. I would also like it to populate the score colum with the appropriate score.Golftrip.xls

Please be aware that the main score collums are set for conditional formating for skins.

[Cheeky Charlie]

Why don't you just sort the table?

[caioz1jp]

While that would be simple enough for me i would like the list to do it automaticly so the person incharge of this will have to do as little as possible.

[Cheeky Charlie]

Well, there's a danger of creating something more complicated to manage by trying to make it simple, I wouldn't do it. Nonetheless, I think there is a relatively elegant solution.

Change the formula in L4 to this:
=SUM(C4:K4)+ROW(1:1)/10000
and copy down
Then format the column number format to show 'number' with 0 decimal places. This adds a fractional value to each score related to the entry's (arbitrary) place in the table, so each entry is unique for indexing (your problem) without changing the look of the spreadsheet. If you need the exact number, you can always use int(score + fraction) later.

You now have your people in order, but you can make the spreadsheet simpler by putting the scores in first, then looking up the people against the scores:
AB4=SMALL($L$4:$L$27,ROW(1:1))
(format columns as 'number' with 0 decimal places)
Which allows:
AA4=INDEX($A$4:$A$27,MATCH(AB4,$L$4:$L$27,0))

Incidentally, your 'if(row()>28' construct seems unnecessary, AA4 could just as easily have been: {=INDEX(A:A,MATCH(SMALL(L:L,ROW(1:1)),L:L,0),1)} given you did not copy it beyond the end of the table. this is redundant if you change the formulae as outlined above - just a note on unnecessary complexity.

[caioz1jp]

I was a little over complicated lol. Thanks, that seems to have everyhting working in order. So each player is basicly assign a small additional sum that is based on their row to distiguinsh them from the others.? So there is no duplicates nor is it large enough to cause rounding of the numbers.?

[Cheeky Charlie]

Exactly. If you get more than 4,999 players you'll get problems, I figured that wouldn't be an issue
Please mark the thread as solved if it is.