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