I am trying to automate as far as possible the forms for a golf competition.

I have a worksheet (Players) with the following columns:
A Sponsor
B Player 1
C Player 2
D Tee Time

As applications come in I input the players names against the relevant
sponsor which have already been listed in Col A and allocate a tee time in
Col D from a data validation list which only allows each time to be used
twice e.g for 4 players from 2 groups.

That all works fine.

I have a second sheet (Tee Times) which is the list of tee times and the
columns here a
A Tee Time
B Sponsor
C Player 1
D Player 2
E Sponsor
F Player 1
G Player 2

What I want to achieve is to look up the first instance of each tee time in
Col A on the Tee Times sheet in Col D of the Players sheet and return (left
lookup) the Sponsor, Player 1 and Player 2 in Cols B, C, and D and put these
in Cols B, C and D on the Tee Times sheet. Then look up the second instance
of each tee time in Col A on the Tee Times sheet in Col D of the Players
sheet and return (left lookup) the Sponsor, Player 1 and Player 2 in Cols B,
C, and D and put these in Cols E, F and G on the Tee Times sheet. I hope
this makes sense.

Originally I was hoping to do this formulaically rather than with a macro
but in the end decided a macro might be the easiest solution hence posting to
this group rather than Worksheet Functions.

Any help with a formulaic or macro solution would be much appreciated

Thanks a lot in anticipation

Sort "Players" by "TeeTime" and then loop selecting data from paired records
i.e. ! &2, 3 & 4 to produce your "Tee Times" sheet rather than do lookups?

Or if you need to, use MATCH of "TEE TIMES" to get players; MATCH will find
the first record of the pair.

"nospaminlich" wrote:

I am trying to automate as far as possible the forms for a golf competition.

I have a worksheet (Players) with the following columns:
A Sponsor
B Player 1
C Player 2
D Tee Time

As applications come in I input the players names against the relevant
sponsor which have already been listed in Col A and allocate a tee time in
Col D from a data validation list which only allows each time to be used
twice e.g for 4 players from 2 groups.

That all works fine.

I have a second sheet (Tee Times) which is the list of tee times and the
columns here a
A Tee Time
B Sponsor
C Player 1
D Player 2
E Sponsor
F Player 1
G Player 2

What I want to achieve is to look up the first instance of each tee time in
Col A on the Tee Times sheet in Col D of the Players sheet and return (left
lookup) the Sponsor, Player 1 and Player 2 in Cols B, C, and D and put these
in Cols B, C and D on the Tee Times sheet. Then look up the second instance
of each tee time in Col A on the Tee Times sheet in Col D of the Players
sheet and return (left lookup) the Sponsor, Player 1 and Player 2 in Cols B,
C, and D and put these in Cols E, F and G on the Tee Times sheet. I hope
this makes sense.

Originally I was hoping to do this formulaically rather than with a macro
but in the end decided a macro might be the easiest solution hence posting to
this group rather than Worksheet Functions.

Any help with a formulaic or macro solution would be much appreciated

Thanks a lot in anticipation