Help distinguishing players with the same score (Alphabetically)

**Marvo** · 03-23-2024, 09:29 AM

In the attached workbook on sheet named "Master" in Column AW it gives the position of a player after a golf event. It uses the helper column AV. When there is no data input, 1st place is awarded to the first named person who is playing in the event, shown by a tick in Column A. So its done alphabetically. If you entered a score of 4 in E7 then the player named Baxter would be shown as first.

However, if you then put a 4 in E20 another player (Grout) has the same score and now both players are shown as 1st. This I can't have as other formula in the workbook will be searching for who is 2nd and of course it wont find one. What I need (via the helper column I assume) is to distinguish between the two players with the same score, again alphabetically is fine.

Otherwise the only other solution I can think of is to have a second helper column and designate each player a number.

My next question would be is there a better way to formulate the helper column as I need to add further rows and I'll end up having /100000000000000000000 by the time I'm finished.

Many thanks.

**Glenn Kennedy** · 03-23-2024, 10:10 AM

Please explain what the purpose of the helper column (AV) is. It seems to cause more problems than it solves!!

Without understanding what you think it's doing... Av4 copied down:

=IF(A4=UNICHAR(10006),"",AQ4+ROW(AQ4)/10^10+AR4/10+AS4/100+AT4/10^3+AU4/10^4+COLUMN(B4)/100000^7-ROW(AV4)/1000000^8)

How a helper column assigns ranks to a series of zero values is beyond me!!

**Marvo** · 03-23-2024, 10:28 AM

Hi Glenn. If I get two total scores equal, we move on to see scored the most points on the hardest hole (stroke index 1) to decide what position someone has finished in. (no ties) If that doesn't decide it, we move on to Stroke index 2, and so on, until we find a winner.

So I have a Result sheet, fills in automatically looking for who comes 1st, 2nd, 3rd and so on. If there are no scores entered I need it to list the names alphabetically, so the helper column gives each name a fraction of 1 so although this doesn't amount to a single point and has no affect at the end of the competition, it does sort the names in alphabetical order.

Your solution works just fine. Thank you very much.

Actually, it sorts them from Z to A and while I'd prefer them to be A -Z I guess it doesn't really matter, as long as it differentiates.

**josephteh** · 03-23-2024, 10:46 AM

Another way, AV4 copy down:
=IF(A4=UNICHAR(10006),"",AQ4+ROW(AQ4)/10^10+AR4/10+AS4/100+AT4/10^3+AU4/10^4-COUNTA(B$4:B4)/10^5)

**Marvo** · 03-23-2024, 10:51 AM

That's done it Joseph, thank you.

**DJunqueira** · 03-23-2024, 10:58 AM

I have looked for your file and there is many instances that you could use a spilled array range to avoid eventual mistakes and reduce maintenance attention, for those situations I highlighted in Yellow.
Like =G4:I28

Also, why two round formulas in Handicaps!I4?

**DJunqueira** · 03-23-2024, 11:18 AM

For the very laborious formula that you have in Master!AO4 and Master!AQ4.

Formula:

Please Login or Register  to view this content.

**Marvo** · 03-23-2024, 11:40 AM

Thank you Djunqueira, I have made all those changes you suggested. You are just what I need, I'm always looking to improve things. I didn't know about spilled arrays, I hope if I have to add or delete rows it wont make any difference. I'm much obliged.

**DJunqueira** · 03-23-2024, 11:46 AM

Why not see the character you are dealing with?
Master!A4

Formula:

Please Login or Register  to view this content.

Master!AR4:AV4 and Master!AZ4

Formula:

Please Login or Register  to view this content.

Master!BI4

Formula:

Please Login or Register  to view this content.

For the column Master!BJ use this spill array formula

Formula:

Please Login or Register  to view this content.

**DJunqueira** · 03-23-2024, 11:51 AM

If you delete or add new row in between the range limits of the formula it won't matter, but if you add or delete at the end of it you will need to check.

**DJunqueira** · 03-23-2024, 12:27 PM

Another spill array formula
Master!C and J columns

Formula:

Please Login or Register  to view this content.

You don't need to multiply by 1 Master!F,H and so on...

Formula:

Please Login or Register  to view this content.

Master!AW:AX RANK() is a function that is sort of condemned by M$, better use the substitute RANK.EQ()

Formula:

Please Login or Register  to view this content.

Master!AZ to BC columns

Formula:

Please Login or Register  to view this content.

**Marvo** · 03-23-2024, 12:30 PM

I've put all the changes you suggested previously into my original and all work well except for the column Master!BJ, I'm getting the error Spill range isn't blank.

**Marvo** · 03-23-2024, 12:37 PM

I'm getting more spill errors now.

**Marvo** · 03-23-2024, 12:44 PM

Maybe the best thing for me to do is wait until you are finished, then make your final workbook mine. All your changes will work, I can then make any changes I need to. Would that be okay?

**AliGW** · 03-23-2024, 12:46 PM

The areas below spill formulae need to be completely free - any obstacle will prevent the formula from spilling.

**Marvo** · 03-23-2024, 12:48 PM

We're working with the same workbook Ali, they are working for DJ. Same stuff underneath.

**AliGW** · 03-23-2024, 12:53 PM

Sigh!!!

You are hard work, Marvo.

If you are getting a spill error, there isn't enough room for the spill - FACT. So you must have done something wrong with the formula.

**DJunqueira** · 03-23-2024, 12:55 PM

Last of the day.
Team!C2

Formula:

Please Login or Register  to view this content.

**DJunqueira** · 03-23-2024, 01:00 PM

Marvo, Handicaps!AC64 has no zero, put a zero there and lets see about the erros.

**DJunqueira** · 03-23-2024, 01:03 PM

Your formula in Master!O70 seem to be inconsistent, I will let for you to fix it or not.

Formula:

Please Login or Register  to view this content.

**Marvo** · 03-23-2024, 01:06 PM

Okay DJ, thank you. I'll close every other workbook down and start again we're your latest offer. Thank you for all your help, it is much appreciated.

**DJunqueira** · 03-23-2024, 01:13 PM

Tks for the feedback, glad to have helped.

Help distinguishing players with the same score (Alphabetically)

LinkBack

Thread Tools

Rate This Thread

Display

Help distinguishing players with the same score (Alphabetically)

Re: Help distinguishing players with the same score (Alphabetically)

Re: Help distinguishing players with the same score (Alphabetically)

Re: Help distinguishing players with the same score (Alphabetically)

Re: Help distinguishing players with the same score (Alphabetically)

Re: Help distinguishing players with the same score (Alphabetically)

Re: Help distinguishing players with the same score (Alphabetically)

Re: Help distinguishing players with the same score (Alphabetically)

Re: Help distinguishing players with the same score (Alphabetically)

Re: Help distinguishing players with the same score (Alphabetically)

Re: Help distinguishing players with the same score (Alphabetically)

Re: Help distinguishing players with the same score (Alphabetically)

Re: Help distinguishing players with the same score (Alphabetically)

Re: Help distinguishing players with the same score (Alphabetically)

Re: Help distinguishing players with the same score (Alphabetically)

Re: Help distinguishing players with the same score (Alphabetically)

Re: Help distinguishing players with the same score (Alphabetically)

Re: Help distinguishing players with the same score (Alphabetically)

Re: Help distinguishing players with the same score (Alphabetically)

Re: Help distinguishing players with the same score (Alphabetically)

Re: Help distinguishing players with the same score (Alphabetically)

Re: Help distinguishing players with the same score (Alphabetically)

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] List winning team's name and players based on lowest daily score

Excel Formula to sum players with lowest winning score

Calculate the number of time two players have the same score in each game week over 10wks

[SOLVED] VBA function for score difference among players

turn players scores into points and attach money worth to that score

Match golf players name to their score in desscending order of highest winning score.

Counting The Number of Players According To Their Score In A Particular Quarter

Bookmarks

Bookmarks

Posting Permissions