Sorting function with multiple levels

[thedunna]

I would appreciate any help in figuring out how to give a cell a specific value based on multiple sorting comparisons. I will try to explain, and I have attached a sample of my worksheet.

This is a golf match-play scorecard. Each pair of golfers are competing against each other. The "green" 1 indicates a hole that was won and a "red" -1 indicates the hole was lost. If a player wins more that he loses, he wins the match and the total number won by is listed next to the winning player in column AB. Occasionally players will tie, winning and losing the same amount of holes (as is the case with Freeman and Clark), and the totals in column AB will be 0 and 0. The sudden death tie-breaker method I am trying to automate is as follows:

The difficulty of each hole is showing in row 3 above the hole. To break ties only, I would like to compare the players scores against each other starting with the #1 handicap hole (6) and proceeding in order #2 (10), #3 (5), etc... When the first hole is won by a player, a 1 would appear in column P of the winning player and further comparison is not necessary. I know how to copy the formula down to as many matches as I have, but do not know how to create the comparison formula.

I have had so much luck on the website getting help with these types of things and I am greatfull and slowly catching on. Thanks in advance to anyone who can help me with this.

Jim

[JohnTopley]

Try this in

AE4

=MIN(IF(G4:Y4=1,($G$3:$Y$3),99))

Enter with Ctrl+Shift+Enter

Copy to other players

You then want to compare the results for each pair and selected the minimum e.g. MIN(Ae4,AE5)

You cannot put formula in P as you will get circular reference errors

[thedunna]

John,

Thank you very much for the reply. This does not provide the results I am looking for and perhaps I was not detailed enough in my post. In the sample worksheet, the first two players in rows 4 and 5 tied. I would like to be able to go to the #1 handicap hole (which on this course is hole 6 in column L (as seen in row 3). These handicap holes will be different on each course played. Compare the two players scores and if there is a "green" 1 game over and that player receives a 1 in column P. If they are still tied (which in this case they are), you would go to the #2 handicap hole (which on this course is hole 10 in column Q. Since the player in row 4 has a "green" 1 he wins and a 1 will appear in column P. If not, you would continue to the #3 handicap hole and so forth.

I sort of understand the circular reference issues. This formula could be anywhere and I could refer column P to their results with IF statements.

I hope this makes sense. I am obviously a novice.

[JohnTopley]

If you look at my results you will that row 4 has a result of 2 (meaning handicap 2) which the result you want i.e, the winner with lowest handicap ????.
, Player in row 5 has result in handicap 11

As I explained earlier, you cannot place anything in column P so your "1" for the winner will have to placed elsewhere.

basically the formula finds the lowest handicap which has a result of 1 for each player.

[thedunna]

Okay. I'm starting to understand....I think. If you are saying that the number 2 which appears in AE4 represents winning the #2 handicap hole, it works So, if it went to the 5th hole it would display a 5? If that's the case it would work....I think. My last question is...how do I make the number "1" appear in the appropriate P column as a result of the smaller number? You mentioned using MIN function but I'm not sure how to apply it. THANKS. I think I'm making progress.

[JohnTopley]

In AF4

=IF(AE4-AE5<0,1,"")

in AF5

=IF(AE5-AE4<0,1,"")

Copy these as a pair to the other matches.

TO REPEAT: you cannot put the result id P because the formula used to calculate the Tie Break Handicap hole includes P in the range so you will get a circular reference.

You can hide the calculation in AE bu hiding the column (see attached) or moving to a column to the far right of your sheet: in the latter case change the ranges in the formulae above.

[thedunna]

Once again, thanks for you help and your patience but I still cannot figure out how to get the results I need. More detail:

Each match results (between G and Y) are filled by a formula taking data from a scorecard. In most cases, as in my example matches 2 and 3, a winner is determined so your formula (and any tie-breaker) is not necessary. So, I applied =IF(AB4=0,(MIN(IF(G4:Y4=1,($G$3:$Y$3),99)))). This will returns a value of FALSE and no 1 in column AF. This works to visually look down a list of many matches, identify the ties, and see which player won the tie-breaker.

All of these results are posted to another worksheet titled "Brackets". Previously the way I have broken ties, is refer to this worksheet, identify the 0-0 matches, compare the handicap holes, and place a 1 in column P next to the winner. This action in column P causes the Brackets sheet to record "Sudden Death Winner", and adds one stroke to the total. Once again, all of the other matches simply record the outright winner by strokes, 7 up, 4 up, etc...

I am trying to figure out a way to record the "1" as an extra hole won without using column P. I have tried adding it to column Z, but it must be in the array somewhere also. I am puzzled how to accomplish this.

[JohnTopley]

I put this column Z and it placed "1" against the correct player


IF($AE4-$AE5<0,1,"")
=IF($AE5-$AE4<0,1,"")

so I don't understand the issue

Use reference to column Z rather than P in "Brackets": column P has no material significance.