Golf team to individual matches

[hobbsofmi]

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.

[hobbsofmi]

Ok, I've figured out how to get the first guy in the right position by using the tab that I use to input scores and do skins with Vlookup. Then I've tried match/index but it only returned numbers and not text.

See the attached revised file.

Help with Names and Matches.xlsx

[hoyasaxa215]

So, on Sheet 2, you'll manually enter the team numbers (in Cells G1 & I1) and you want the two members from those two teams with the lowest handicaps to be listed in Cells B7 and B8 and the highest two handicaps in Cells B15 & B16? And I assume you want the corresponding handicaps to be populated in Column C?

[hobbsofmi]

hoyasaxa215: Yes, I enter the teams in G1 and I1. I have all the other formulas worked out but automatically putting the name in B7, B8, B15 and B16. I've just stripped them out to keep the file simple.

[hoyasaxa215]

Okay, so I had to make some modifications to get this to work. I had to reorganize the table in Sheet 1 so there were no blank rows breaking up the data. Now the names should autopopulate when you enter the team numbers.

I kept everything visible so you could see how it works, but when you're done with that, you can
1.HIDE (not delete) Column C in Sheet 1, and
2. HIDE (not delete) Column A in Sheet 2, or simply remove the yellow highlighting and change the text color to white so it's not visible when you print it out.

Let me know if you have any questions or if this doesn't work for you.

[hobbsofmi]

Yes, that works but I think with index/match it can be done without adding more to a very complex spreadsheet. It does a league for 20 weeks and keeps all the stats, points, strokes, skins, etc. But if I can't get index/match to work than I'll go this route.

Thanks

[mrmmickle1]

Hobbesofmi,

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

Please Login or Register  to view this content.

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

Worksheet Formulas for Sheet2:

Cell 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)

The Answer is clearer on this link: CLICK HERE

[hobbsofmi]

Yes, this works but I have 10 matches for 20 weeks to do. So that 800 places.

[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.

[hobbsofmi]

I've got some to work but not all of them. So I went back to trying Index/match. I got the first ones just like I did for VLOOKUP but not the second one. The formula analizer says the right answer but I get a #VALUE! error which I can't seem to clear on Sheet 3. If I can get that to work I know I can get the rest.

Help with Names and Matches.xlsx

[hobbsofmi]

I found one way to do it with INDEX, SMALL and ROW that was the same formula in my last update: {=INDEX(Sheet3!C9:C56,SMALL(IF(Sheet3!B9:B56=I1,ROW(Sheet3!$B$8:$B$55)-ROW(Sheet3!$B$8)+1),2))}

The {} is the key that tells the formula that it's not a value. You can't type the {} in the formula but you must do a CTRL SHIFT ENTER when in the formula. Saving in once you do it then you can change the team number on the top and it auto puts in both team members in the right spot. You can do it for all 10 matches for 20 weeks ahead of time and as things changes I've changed the area that it's looking and it's done. See the final attached file.

[mrmmickle1]

Glad you were able to get it all sorted out! Thanks for sharing the solution.

[hobbsofmi]

mrmmickle1: Thanks for you help too. Your solution did also work but it was just not easy for 10 matches for 20 weeks and multiple tabs.

Now that we are at the end of the season I'll have the data to make sure all the formula's are on all tabs and be ready to blank it for next year.

Oh...how do you mark this solved?

[mrmmickle1]

Not sure you can mark it solved. Might not be an option or it could be a moderator right.....