Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extracting matching data
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting the values matching to multiplr columns. | Excel Discussion (Misc queries) | |||
Matching identical data using data only once in the matching proce | Excel Discussion (Misc queries) | |||
Extracting Data for .Txt Files By Unique Field Data | Excel Discussion (Misc queries) | |||
Matching data and linking it to the matching cell | Links and Linking in Excel | |||
extracting matching data | Excel Programming |