Need to populate table with golf scores

[Rock5150]

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 A	James Smith	John Doe	Tony Jones	Dave Johnson
James Smith	(would be blank)
John Doe		blocked
Tony Jones			Blocked
Dave Johnson				Blocked

 

[Rock5150]

If a Vlookup is needed, i can rename in the second table and use/add the word "Net" for vlookup purposes.

 

[Peter_SSs]

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]

Here is the data in xl2BB format

Ham and Egg spreadsheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Name	Hole 1	Hole 2	Hole 3	Hole 4	Hole 5	Hole 6	Hole 7	Hole 8	Hole 9	Hole 10	Hole 11	Hole 12	Hole 13	Hole 14	Hole 15	Hole 16	Hole 17	Hole 18	Handicap	Total
2	Handicap	13	1	5	7	9	2	10	11	12	17	18	3	4	6	8	14	15	16
3
4	John Doe	4	5	4	4	3	6	4	5	5	8	4	4	3	4	5	5	5	6		84
5	John Doe Net	4	4	3	3	3	5	4	5	5	8	4	3	2	3	4	5	5	6	8	76
6
7	James Smith	4	5	4	4	3	6	4	5	5	8	4	4	3	4	5	5	5	6		84
8	James Smith Net	2	2	2	2	1	3	2	3	3	6	2	1	0	2	3	3	3	4	40	44
9
10	Tony Jones	4	5	4	4	3	6	4	5	5	8	4	4	6	4	5	5	5	6		87
11	Tony Jones Net	3	4	3	3	2	5	3	4	4	8	4	3	5	3	4	4	4	5	16	71
12
13	Dave Johnson	4	5	4	4	3	6	4	5	5	8	4	4	3	3	5	5	5	6		83
14	Dave Johnson Net	3	4	3	3	2	5	3	4	4	8	4	3	2	2	4	5	5	6	13	70
Scores

Cell Formulas
Range	Formula
U13:U14,U10:U11,U7:U8,U4:U5	U4	=SUM(B4:S4)
B5:S5,B14:S14,B11:S11,B8:S8	B5	=B4-(B$2<=MOD($T5,18))-INT($T5/18)
T5,T14,T11,T8	T5	=VLOOKUP(A4,Handicap!A:B,2,FALSE)

 

[Rock5150]

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
	A	B	C	D	E
1		James Smith	John Doe	Tony Jones	Dave Johnson
2	James Smith
3	John Doe
4	Tony Jones
5	Dave Johnson
Grid

 

[lrobbo314]

If you have the 'Let' function available.

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	John Doe	4	5	4	4	3	6	4	5	5	8	4	4	3	4	5	5	5	6		84			James Smith	John Doe	Tony Jones	Dave Johnson
2	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		44	44	44
3																							John Doe	44		68	70
4	James Smith	4	5	4	4	3	6	4	5	5	8	4	4	3	4	5	5	5	6		84		Tony Jones	44	68		67
5	James Smith Net	2	2	2	2	1	3	2	3	3	6	2	1	0	2	3	3	3	4	40	44		Dave Johnson	44	70	67
6
7	Tony Jones	4	5	4	4	3	6	4	5	5	8	4	4	6	4	5	5	5	6		87
8	Tony Jones Net	3	4	3	3	2	5	3	4	4	8	4	3	5	3	4	4	4	5	16	71
9
10	Dave Johnson	4	5	4	4	3	6	4	5	5	8	4	4	3	3	5	5	5	6		83
11	Dave Johnson Net	3	4	3	3	2	5	3	4	4	8	4	3	2	2	4	5	5	6	13	70
Sheet5

Cell Formulas
Range	Formula
X2:AA5	X2	=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))))

 

[lrobbo314]

And if you don't have 'Let'...

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	John Doe	4	5	4	4	3	6	4	5	5	8	4	4	3	4	5	5	5	6		84			James Smith	John Doe	Tony Jones	Dave Johnson
2	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		44	44	44
3																							John Doe	44		68	70
4	James Smith	4	5	4	4	3	6	4	5	5	8	4	4	3	4	5	5	5	6		84		Tony Jones	44	68		67
5	James Smith Net	2	2	2	2	1	3	2	3	3	6	2	1	0	2	3	3	3	4	40	44		Dave Johnson	44	70	67
6
7	Tony Jones	4	5	4	4	3	6	4	5	5	8	4	4	6	4	5	5	5	6		87
8	Tony Jones Net	3	4	3	3	2	5	3	4	4	8	4	3	5	3	4	4	4	5	16	71
9
10	Dave Johnson	4	5	4	4	3	6	4	5	5	8	4	4	3	3	5	5	5	6		83
11	Dave Johnson Net	3	4	3	3	2	5	3	4	4	8	4	3	2	2	4	5	5	6	13	70
Sheet5

Cell Formulas
Range	Formula
X2:AA5	X2	=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))))