Need to populate table with golf scores

Rock5150

New Member
Joined
Nov 16, 2012
Messages
38
Office Version
  1. 365
Platform
  1. Windows
All

This is a new thread. I have the below spreadsheet where the scores are calculated for each golfer. How do i "combine" a pair of golfers so that the total net is calculated? So for each pair of golfers, between the two, i would take the lowest score (MIN function) between the two and use that for each hole (18 holes of golf)

So i already have the scores for each person listed below in the table. I want to pull just each person's "NET" however for a pair, i need to take the lowest score per hole between each pair. For example on the below with John Doe and James smith, the lowest score on hole 1 is "2" (James).

If i have John and TOny as a pair, the low score say on hole 13 would be a "2" (John).

So i want to see if a formula can be created so that the second table below is filled in with the total combined score. The combined score is the sum of each MIN score for the pair for 18 holes then come up with a total.

So say the combined total for John and Tony would be 68 . That would then be filled in the second "grid" table below (Cell C4)

John Doe
4​
5​
4​
4​
3​
6​
4​
5​
5​
8​
4​
4​
3​
4​
5​
5​
5​
6​
84​
John Doe Net
4​
4​
3​
3​
3​
5​
4​
5​
5​
8​
4​
3​
2​
3​
4​
5​
5​
6​
8​
76​
James Smith
4​
5​
4​
4​
3​
6​
4​
5​
5​
8​
4​
4​
3​
4​
5​
5​
5​
6​
84​
James Smith Net
2​
2​
2​
2​
1​
3​
2​
3​
3​
6​
2​
1​
0​
2​
3​
3​
3​
4​
40​
44​
Tony Jones
4​
5​
4​
4​
3​
6​
4​
5​
5​
8​
4​
4​
6​
4​
5​
5​
5​
6​
87​
Tony Jones Net
3​
4​
3​
3​
2​
5​
3​
4​
4​
8​
4​
3​
5​
3​
4​
4​
4​
5​
16​
71​
Dave Johnson
4​
5​
4​
4​
3​
6​
4​
5​
5​
8​
4​
4​
3​
3​
5​
5​
5​
6​
83​
Dave Johnson Net
3​
4​
3​
3​
2​
5​
3​
4​
4​
8​
4​
3​
2​
2​
4​
5​
5​
6​
13​
70​

Column AJames SmithJohn DoeTony JonesDave Johnson
James Smith(would be blank)
John Doeblocked
Tony JonesBlocked
Dave JohnsonBlocked
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Rock5150

New Member
Joined
Nov 16, 2012
Messages
38
Office Version
  1. 365
Platform
  1. Windows
If a Vlookup is needed, i can rename in the second table and use/add the word "Net" for vlookup purposes.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,452
Office Version
  1. 365
Platform
  1. Windows
Please update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

It would also make it easier for you helpers, if you posted your sample data with XL2BB
 

Rock5150

New Member
Joined
Nov 16, 2012
Messages
38
Office Version
  1. 365
Platform
  1. Windows
Here is the data in xl2BB format

Ham and Egg spreadsheet.xlsx
ABCDEFGHIJKLMNOPQRSTU
1NameHole 1Hole 2Hole 3Hole 4Hole 5Hole 6Hole 7Hole 8Hole 9Hole 10Hole 11Hole 12Hole 13Hole 14Hole 15Hole 16Hole 17Hole 18HandicapTotal
2Handicap131579210111217183468141516
3
4John Doe45443645584434555684
5John Doe Net443335455843234556876
6
7James Smith45443645584434555684
8James Smith Net2222132336210233344044
9
10Tony Jones45443645584464555687
11Tony Jones Net3433253448435344451671
12
13Dave Johnson45443645584433555683
14Dave Johnson Net3433253448432245561370
Scores
Cell Formulas
RangeFormula
U13:U14,U10:U11,U7:U8,U4:U5U4=SUM(B4:S4)
B5:S5,B14:S14,B11:S11,B8:S8B5=B4-(B$2<=MOD($T5,18))-INT($T5/18)
T5,T14,T11,T8T5=VLOOKUP(A4,Handicap!A:B,2,FALSE)
 

Rock5150

New Member
Joined
Nov 16, 2012
Messages
38
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Here is the second table i need a formula. Let me know if you have any questions to clarify the above.

Ham and Egg spreadsheet.xlsx
ABCDE
1James SmithJohn DoeTony JonesDave Johnson
2James Smith
3John Doe
4Tony Jones
5Dave Johnson
Grid
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,134
Office Version
  1. 365
Platform
  1. Windows
If you have the 'Let' function available.

Book3
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1John Doe45443645584434555684James SmithJohn DoeTony JonesDave Johnson
2John Doe Net443335455843234556876James Smith 444444
3John Doe44 6870
4James Smith45443645584434555684Tony Jones4468 67
5James Smith Net2222132336210233344044Dave Johnson447067 
6
7Tony Jones45443645584464555687
8Tony Jones Net3433253448435344451671
9
10Dave Johnson45443645584433555683
11Dave Johnson Net3433253448432245561370
Sheet5
Cell Formulas
RangeFormula
X2:AA5X2=IF($W2=X$1,"",LET(tbl,$B$1:$S$11,n,$A$1:$A$11,r,INDEX(tbl,MATCH($W2,n,0)+1,0),c,INDEX(tbl,MATCH(X$1,n,0)+1,0),SUM(IF(r<c,r,c))))
 
Solution

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,134
Office Version
  1. 365
Platform
  1. Windows
And if you don't have 'Let'...

Book3
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1John Doe45443645584434555684James SmithJohn DoeTony JonesDave Johnson
2John Doe Net443335455843234556876James Smith 444444
3John Doe44 6870
4James Smith45443645584434555684Tony Jones4468 67
5James Smith Net2222132336210233344044Dave Johnson447067 
6
7Tony Jones45443645584464555687
8Tony Jones Net3433253448435344451671
9
10Dave Johnson45443645584433555683
11Dave Johnson Net3433253448432245561370
Sheet5
Cell Formulas
RangeFormula
X2:AA5X2=IF($W2=X$1,"",SUM(IF(INDEX($B$1:$S$11,MATCH($W2,$A$1:$A$11,0)+1,0)>INDEX($B$1:$S$11,MATCH(X$1,$A$1:$A$11,0)+1,0),INDEX($B$1:$S$11,MATCH(X$1,$A$1:$A$11,0)+1,0),INDEX($B$1:$S$11,MATCH($W2,$A$1:$A$11,0)+1,0))))
 

Watch MrExcel Video

Forum statistics

Threads
1,129,278
Messages
5,635,258
Members
416,850
Latest member
Sidddharth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top