Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
help...too much copying and pasting
[image: http://home.cogeco.ca/~rbhayana/backload.gif]
[image: http://home.cogeco.ca/~rbhayana/mastertable.gif] I ran into a lot of work and am wondering if anyone can help me out. Help would be greatly appreciated because going thru 19272 records for copying and pasting is driving me insane. These are the 2 files I'm dealing with (see pictures above). Top one is backload file and the one underneath is mastertable. As you can see, FundataKey is common to both files. The values of Fundatakey are not repeated in mastertable however are repeated in the backload file. What I would like is, to copy the values (in column C -BackEndTablePercent) in the rows associated with each Fundatakey into the appropriate columns in the master table. For example, in the backload file, for the Fundatakey value 10056, the values 6, 5.5, 5, 4.5, 4, 3.5 and 0 go into AO(BETPercent1stYear), AP(BETPercent2ndYear), AQ(BETPercent3rdYear)...AV(BETPercent8thYear), AW(BETPercentAfter). So essentially I'm somehow gonna have to link the column AO of mastertable with the rows containing the text "In the 1st Year"or "1st" and AP with rows containing the text "In the 2ndYear" or "2nd" and so on). One thing to note is that the the number of years with each FundataKey vary from 1 to 10. I only want to deal with 8 years and then I have a column BETPercentAfter which will store the values of "Thereafter". Some FundataKeys don't have a "Thereafter" row either...in which case, I just want it to copy whatever there is over into the appropriate columns. Another thing to note is t hat not all FunDataKey values in the mastertable are used...if they're not used, then they just don't appear in the Backload file. As mentioned earlier, there are 19272 records and I really don't want to do a copy paste special and transpose on all of these. ------------------------------------------------ Message posted from the Excel Tip Forum at http://www.ExcelTip.com/forum/ -- View and post usenet messages directly from http://www.ExcelTip.com -- Hundreds of free MS Excel tips, tricks and solutions ------------------------------------------------ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
help...too much copying and pasting
I think VLOOKUP function should be helpful.
Best Regards Bill -----Original Message----- [image: http://home.cogeco.ca/~rbhayana/backload.gif] [image: http://home.cogeco.ca/~rbhayana/mastertable.gif] I ran into a lot of work and am wondering if anyone can help me out. Help would be greatly appreciated because going thru 19272 records for copying and pasting is driving me insane. These are the 2 files I'm dealing with (see pictures above). Top one is backload file and the one underneath is mastertable. As you can see, FundataKey is common to both files. The values of Fundatakey are not repeated in mastertable however are repeated in the backload file. What I would like is, to copy the values (in column C -BackEndTablePercent) in the rows associated with each Fundatakey into the appropriate columns in the master table. For example, in the backload file, for the Fundatakey value 10056, the values 6, 5.5, 5, 4.5, 4, 3.5 and 0 go into AO (BETPercent1stYear), AP(BETPercent2ndYear), AQ(BETPercent3rdYear)...AV (BETPercent8thYear), AW(BETPercentAfter). So essentially I'm somehow gonna have to link the column AO of mastertable with the rows containing the text "In the 1st Year"or "1st" and AP with rows containing the text "In the 2ndYear" or "2nd" and so on). One thing to note is that the the number of years with each FundataKey vary from 1 to 10. I only want to deal with 8 years and then I have a column BETPercentAfter which will store the values of "Thereafter". Some FundataKeys don't have a "Thereafter" row either...in which case, I just want it to copy whatever there is over into the appropriate columns. Another thing to note is t hat not all FunDataKey values in the mastertable are used...if they're not used, then they just don't appear in the Backload file. As mentioned earlier, there are 19272 records and I really don't want to do a copy paste special and transpose on all of these. ------------------------------------------------ Message posted from the Excel Tip Forum at http://www.ExcelTip.com/forum/ -- View and post usenet messages directly from http://www.ExcelTip.com -- Hundreds of free MS Excel tips, tricks and solutions ------------------------------------------------ . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying & Pasting | Excel Discussion (Misc queries) | |||
Copying and Pasting | Excel Discussion (Misc queries) | |||
Copying and Pasting | Excel Discussion (Misc queries) | |||
Copying and Pasting | Excel Discussion (Misc queries) | |||
Copying and pasting ??? | Excel Discussion (Misc queries) |