Thread: Extracting matching data

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 are:
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 are:
> 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