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 |
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 |
All times are GMT +1. The time now is 08:55 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com