Need macro that includes a index/ match function

[Grilleman]

Hi -

See my attached sample2 workbook.

I've been struggling to create a macro that automatically matches each name in column F with its corresponding Time, Tee No., Team and Group and write this data to columns S thru AD. As a student of VBA, I look forward to learning how best to accomplish this task as I am stumped! See columns Z thru AD for an illustration of my desired result.

Note also that in a previous post someone in the community gave me an index formula that worked when pasted in a destination cell. However, I need a procedure to accomplish this task behind the scenes as the user will not have access to the columns and data in question. The formulas provided to me though the community follow:

Time =INDEX($C$2:$C$18,INT((ROW()-2)/4)+1)
Tee =INDEX($D$2:$D$18,INT((ROW()-2)/4)+1)
Team =INDEX($A$2:$A$18,INT((ROW()-2)/4)+1)
Group =INDEX($B$2:$B$18,INT((ROW()-2)/4)+1)

Again, thank you for whatever help you are able to provide.

[Bernie Deitrick]

Try just using formulas - I think this is what you want....

[Grilleman]

Thanks Bernie. I will try to use what you have suggested here which seems like it will work great. Also, being a student of VBA I need to change the way I think. Your suggestion to input formulas and copy them down to the nth row, though simple, is not something I even considered. For that, I thank you for the learning.

Best,

Grilleman

[Grilleman]

So I have attempted to implement Bernie's solution and encountered a few issues. Pleasse see the attached Book3.xlsm for a better illuistration.

First, the formulas in columns T thru X referencing column R are not responding correctly when the number of players per group changes or when more than one person has the same last name. See Book3 example.

In another test I inserted only two names in each of the first four tee time slots (see Book3...column G) for a representation. Here again, the formulas do not fire correctly.

FYI: In theory a Group can include as few as just one player (i.e., one player teeing off at 7:30). However, Groups usually include 2 (twosome), 3 (threesome) or 4 players (a foursome). The formulas need to fire regarless the number of players.

Also and for my own education, can you please give me a quick explanation of the components of this formula?

=IF(R2="","",INDEX(D:D,INT((S2-2)/4)*4+5))

I understand the INDEX portion, but I do not get the INT portion. I assume "INT' stands for "Intercept", but in Excel 2010 the "INT" function has to do with rounding. Very confusing to me.

Thanks

[Bernie Deitrick]

My assumption was that your list would be contiguous down column F. This version allows as many or as few players per group as you want to create the alpha list. It also fixes the sorting to include the first name.

The INT means INTEGER, and is used to create math that matches the correct row with the tee times/ team / tee information that is only given every 4 rows.

[Grilleman]

Yep! Seems to be working exactly as desired. Thank you so much!!