Golf team to individual matches

[Hobbesofmi]

Golf teams of 2 for 20 teams and each week the low handicap from each team will match play each other and higher will match play.

I have the roster with the handicaps on one tab and the matches on another. The team number is a merged two rows on the roster.

I've tried match, vlookup, sumif, etc. so I'm still back at square one.

		Match	Absents	Season	Team Standings
FLIGHT "A"	Gross	Hcp	Net	Individual	Team	Weeks Played	Total Strokes	Avg	New HCP	points this week	total points	PLACE
1	JERRY	45	6	39	2	4	8	348	43.50	6	12	215	5
JIM	A	5		2	8	342	42.75	5
2	MIKE	42	5	37	3	5	7	292	41.71	5	19	218	3
LARRY	44	7	37	2	8	353	44.13	7
3	JIM	44	10	34	1	3	9	431	47.89	10	31	225	1
JIM	39	2	37	2	8	310	38.75	2
4	RORY	47	6	41	0	0	10	437	43.70	6	22	215	5
DENNY	53	10	43	0	10	491	49.10	10
5	GLEN	42	8	34	3	5	7	319	45.57	8	32	182	10
JIM	50	12	38	2	8	403	50.38	12
6	WAYNE	46	6	40	3	6	7	307	43.86	6	28	220	2
JOE	46	11	35	3	7	344	49.14	11
7	TOM	45	12	33	0	0	10	500	50.00	11	29	216	4
RICH	47	7	40	0	10	447	44.70	7
8	MIKE	51	10	41	1	3	9	435	48.33	10	20	213	7
PAUL	A	9		2	8	379	47.38	9
9	TIM	47	14	33	2	3	8	418	52.25	13	25	200	9
DAN	46	7	39	1	9	406	45.11	7
10	****	45	9	36	1	1	9	418	46.44	8	21	208	8
ROY	56	15	41	0	10	549	54.90	15
FLIGHT "B"
11	TOM	43	11	32	1	1	9	442	49.11	10	28	209	2
RICK	49	12	37	0	10	513	51.30	12
12	KEVIN	48	10	38	0	0	10	479	47.90	10	19	190	5
KEVIN Jr.	53	13	40	0	10	518	51.80	13
13	CHUCK	48	9	39	0	2	10	474	47.40	9	15	205	3
JACK	56	17	39	2	8	453	56.63	17
14	SCOTT	49	11	38	0	1	10	494	49.40	11	20	210	1
RICHARD	57	12	45	1	9	465	51.67	13
15	MIKE	54	12	42	1	3	9	465	51.67	13	11	181	7
RAY	59	17	42	2	8	458	57.25	17
16	LES	56	15	41	1	2	9	494	54.89	15	12	176	9
STEVE	A	18		1	9	522	58.00	18
17	BILL	A	11		4	11	6	295	49.17	11	0	133	10
GARIE	A	19		7	3	185	61.67	19
18	BOB	59	15	44	3	4	7	388	55.43	16	18	178	8
WHIT	62	19	43	1	9	538	59.78	19
19	JIM	55	13	42	2	4	8	425	53.13	14	9	193	4
GARY	55	16	39	2	8	450	56.25	16
20	JOHN	51	17	34	0	1	9	504	56.00	16	21	189	6
NAVID	55	15	40	1	6	326	54.33	15

<colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:7716;width:158pt" width="211"> <col style="width:48pt" span="3" width="64"> <col style="mso-width-source:userset;mso-width-alt:3437;width:71pt" width="94"> <col style="width:48pt" span="8" width="64"> </colgroup><tbody>
</tbody>

MATCH NO.	1			Team	8	Team	11				5:00					FRONT	NINE
		BLUE	506	380	358	305	392	361	232	174	523	3231
		WHITE	493	365	342	300	374	346	220	169	503	3112			INDIVIDUAL	TEAM #	8
		RED	473	345	296	296	344	320	207	162	397	2840			POINTS	POINTS
ABS.		POINTS	0	1	2	1	2	2	1	1	1			2	13	29
		INDV. HOLE H'CAP	0	0	0	0	0	0	0	0	0
	PAUL	47.38	0		7	4	6	6	4	5	7	4	4	47	8	39
	TOM	49.11	0		5	4	8	6	7	6	7	4	5	52	9	43
		INDV. HOLE H'CAP	0	0	0	0	0	0	0	0	1			0	7
		POINTS	2	1	0	1	0	0	1	1	1
		Average	Tot. Abs.	HOLE	1	2	3	4	5	6	7	8	9	OUT	H'CAP.	NET	INDIVIDUAL	Team #	11
		HANDICAP	6	9	7	5	4	3	2	8	1	POINTS	POINTS
ABS.		POINTS	0	2	2	2	2	1	1	2	2			2	16	11
		INDV. HOLE H'CAP	0	0	0	0	0	0	0	0	0
	MIKE	48.33	0		8	4	6	4	5	4	7	4	6	48	9	39
	RICK	51.30	0		6	6	7	5	6	4	7	5	8	54	10	44
		INDV. HOLE H'CAP	0	0	0	0	0	0	0	0	1			0	4
		POINTS	2	0	0	0	0	1	1	0	0
		PAR	5	3	5	4	4	4	4	3	4	36

<colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:6363;width:131pt" width="174"> <col style="width:48pt" span="18" width="64"> </colgroup><tbody>
</tbody>

 

[XLfriend777]

Hi Hobbesofmi - Welcome to the forum. As nobody has responded I am hoping to get things started. You might try unmerging all merged cells. Merged cells can often confuse functions and any VBA macros you may try. Hope this helps get things started.

 

[Hobbesofmi]

I can unmerge the cell but then that sheet will not look good in my opinion.

I was playing with another tab that I do all the scores and skins on that list all the players and their averages. I've put the team number in front of their name 1a 1b but the letter has messed the formula.

Here is what that tab looks like for one week:

WEEK NO 15: Front 9		# Players in Skins =		0		Gross Skin Pot =	0			No. of Skins =		0		Payout per Skin:		###
					SINGLE LOW SCORE>>	0	0	0	0	0	0	0	0	0				SKINS>>	0	0	0	0	0	0	0	0	0
					Hole>>	1	2	3	4	5	6	7	8	9				Hole>>	1	2	3	4	5	6	7	8	9
					Par>>	5	3	5	4	4	4	4	3	4				Par>>	5	3	5	4	4	4	4	3	4
					HDCP>>>	6	9	7	5	4	3	2	8	1				HDCP>>>	6	9	7	5	4	3	2	8	1
Paid		PLAYERS	Average	H.C.	GROSS SCORE									OUT		SKIN SCORE										OUT
		JIM	38.42	2											0													0
		MIKE	41.00	4											0													0
		JERRY	42.75	5											0													0
		JIM	43.55	6											0													0
		RORY	43.75	6											0													0
		LARRY	44.58	7											0													0
		WAYNE	44.67	7											0													0
		RICH	44.71	7											0													0
		DAN	44.73	7											0													0
		GLEN	46.00	8											0													0
		****	46.23	8											0													0
		PAUL	47.50	9											0													0
		KEVIN	47.57	9											0													0
		CHUCK	47.86	9											0													0
		JIM	48.08	10											0													0
		JOE	48.55	10											0													0
		MIKE	48.58	10											0													0
		DENNY	48.93	10											0													0
		BILL	49.25	11											0													0
		TOM	49.69	11											0													0
				# Players in Skins =		0		Gross Skin Pot =	0			No. of Skins =		0						Payout per Skin:	###
					SINGLE LOW SCORE>>	0	0	0	0	0	0	0	0	0				SKINS>>	0	0	0	0	0	0	0	0	0
					Hole>>	1	2	3	4	5	6	7	8	9				Hole>>	1	2	3	4	5	6	7	8	9
					Par>>	5	3	5	4	4	4	4	3	4				Par>>	5	3	5	4	4	4	4	3	4
					HDCP>>>	6	9	7	5	4	3	2	8	1				HDCP>>>	6	9	7	5	4	3	2	8	1
Paid		PLAYERS	Average	H.C.	GROSS SCORE									OUT		SKIN SCORE										OUT
		TOM	49.21	11											0													0
		SCOTT	49.57	11											0													0
		JIM	49.67	11											0													0
		MIKE	50.15	11											0													0
		RICK	50.93	12											0													0
		TIM	51.08	12											0													0
		KEVIN Jr.	51.46	12											0													0
		RICHARD	52.33	13											0													0
		JIM	53.00	14											0													0
		NAVID	53.33	14											0													0
		ROY	53.57	14											0													0
		LES	54.00	14											0													0
		JOHN	54.08	14											0													0
		JACK	54.67	15											0													0
		GARY	55.27	15											0													0
		BOB	55.56	16											0													0
		RAY	56.17	16											0													0
		STEVE	57.00	17											0													0
		WHIT	60.08	19											0													0
		GARIE	61.50	19											0													0

<tbody>
</tbody>

 

[Hobbesofmi]

Ok...with a Vlookup I can get the low Handicap guy in the right spot. =VLOOKUP(I1,Sheet3!B9:D56,2,FALSE)

So now I have to figure out how to get the second guy in the second spot. I tried index/match but it will only return numbers and not text. So does someone know a trick to return text?

 

[mrmmickle1]

A combination of the two functions Index and Match will return Text Values.

See Below:

Excel 2012
	A	B
1	TextExample	Location ID
2	Text 1	1234
3	Text 2	526
4	Text 3	324
Text

Here are the formulas:

Excel 2012
	A	B
1	Index Match Formulas	Location ID
2	Text 1	1234
3	Text 1	1234
4	Text 2	526
5	Text 2	526
6	Text 3	324
7	Text 3	324
FormulaSheet

Cell Formulas
Range	Formula
A2	=INDEX(Text!A:B, MATCH(FormulaSheet!B2,Text!B:B,0), 1)
A3	=INDEX(Text!A:B, MATCH(FormulaSheet!B3,Text!B:B,0), 1)
A4	=INDEX(Text!A:B, MATCH(FormulaSheet!B4,Text!B:B,0), 1)
A5	=INDEX(Text!A:B, MATCH(FormulaSheet!B5,Text!B:B,0), 1)
A6	=INDEX(Text!A:B, MATCH(FormulaSheet!B6,Text!B:B,0), 1)
A7	=INDEX(Text!A:B, MATCH(FormulaSheet!B7,Text!B:B,0), 1)

It may be easier to help if you condense your example. That's a lot of data to look at.

 

[Hobbesofmi]

I was looking to upload the file but don't see a way to do it so yes, it's a lot of data and I'm new so I know I'm not formatting it right on here, so sorry about that. I've tried match/index so I'll try it your way and see if it works.

 

[mrmmickle1]

Hobbesofmi,

No worries. We are all here to help. Let me know how the INDEX /MATCH formula goes. If it doesn't work then post back. We will get your issue sorted out

1. I'm not saying that this always happens, but sometimes people who would potentially help resolve your issue may skip over the thread because the overwhelming amount of data. A lot of times you can get the issue your experiencing across with a small sample data set.

2. If you want to post your workbook you will have to upload it to an external site like DropBox or SkyDrive and then post a link.

 

[mrmmickle1]

Hobbesofmi,

Insert this User Defined Function (UDF) code in a standard module:

[COLOR=#0000ff]Function[/COLOR] Nth_Occurrence(range_look A[COLOR=#0000ff]s[/COLOR] Range, find_it[COLOR=#0000ff] As String[/COLOR], occurrence [COLOR=#0000ff]As Long[/COLOR], offset_row [COLOR=#0000ff]As Long[/COLOR], offset_col [COLOR=#0000ff]As Long[/COLOR])


[COLOR=#0000ff]Dim[/COLOR] lCount [COLOR=#0000ff]As Long[/COLOR]
[COLOR=#0000ff]Dim [/COLOR]rFound [COLOR=#0000ff]As [/COLOR]Range

   [COLOR=#0000ff] Set[/COLOR] rFound = range_look.Cells(1, 1)

      [COLOR=#0000ff]  For[/COLOR] lCount = 1 [COLOR=#0000ff]To[/COLOR] occurrence

          [COLOR=#0000ff]  Set[/COLOR] rFound = range_look.Find(find_it, rFound, xlValues, xlWhole)

[COLOR=#0000ff]        Next[/COLOR] lCount

    Nth_Occurrence = rFound.Offset(offset_row, offset_col)

[COLOR=#0000ff]End Function[/COLOR]

This function is provided on the OzGrid Website

In order to accomplish this:
1. Hit Alt + F11
2.On the Menu Go To Insert > Module
3. In the Project Explorer you will now see an icon named Module 1. Double Click It.
4. Paste the above code on the white screen

Once this is accomplished use these formulas on your sheet assuming the data to pull from looks like this:
DATA TO PULL FROM:

Excel 2012
	A	B	C	D
7	Paid	Team	PLAYERS	Average
8		3	JIM	38.42
9		2	MIKE	41.00
10		1	JERRY	42.75
11		1	JIM	43.55
12		4	RORY	43.75
13		2	LARRY	44.58
14		6	WAYNE	44.67
15		7	RICH	44.71
16		9	DAN	44.73
17		5	GLEN	46.00
18		10	****	46.23
19		8	PAUL	47.50
20		12	KEVIN	47.57
21		13	CHUCK	47.86
22		3	JIM VAN	48.08
23		6	JOE	48.55
24		8	MIKE	48.58
25		4	DENNY	48.93
26		17	BILL	49.25
27		11	TOM	49.69
28		11	RICK	???
Sheet3

FORMULAS LOOK LIKE THIS:

Excel 2012
	A	B	C	D	E	F	G	H	I
1	MATCH NO.		1			Team	8	Team	11
2			BLUE			506	380	358	305
3			WHITE			493	365	342	300
4			RED			473	345	296	296
5	ABS.		POINTS			0	1	2	1
6			INDV. HOLE H'CAP			0	0	0	0
7		PAUL	47.38	0		7	4	6	6
8		TOM	49.11	0		5	4	8	6
9			INDV. HOLE H'CAP			0	0	0	0
10			POINTS			2	1	0	1
11			Average	Tot. Abs.	HOLE	1	2	3	4
12					HANDICAP	6	9	7	5
13	ABS.		POINTS			0	2	2	2
14			INDV. HOLE H'CAP			0	0	0	0
15		MIKE	48.33	0		8	4	6	4
16		RICK	51.30	0		6	6	7	5
17			INDV. HOLE H'CAP			0	0	0	0
18			POINTS			2	0	0	0
19			PAR			5	3	5	4
Sheet2

Cell Formulas
Range	Formula
B7	=Nth_Occurrence(Sheet3!B8:B28,Sheet2!G1,1,0,1)
B8	=Nth_Occurrence(Sheet3!B8:B28,Sheet2!I1,1,0,1)
B15	=Nth_Occurrence(Sheet3!B8:B28,Sheet2!G1,2,0,1)
B16	=Nth_Occurrence(Sheet3!B8:B28,Sheet2!I1,2,0,1)

This is what you wanted correct??

 

[Hobbesofmi]

Yes, this works but I have 10 matches for 20 weeks so 800 places to put it.

 

[mrmmickle1]

If you can identify a set pattern then you can just programmatically insert the formulas. For example the two formulas above are 8 cells apart. Is this how your data is set up for an entire sheet? Or do you have 200 sheets containing these 4 formulas? Either way it is possible to programmatically insert formulas. If you can provide a pattern I can help you accomplish your goal.