R
rahulb
[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 MasterTable's columns
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). I
would also have it search the mastertable for the appropriate
Fundatakey. I don't know much about programming in excel but I do know
that a counter can not be used as Fundatakey is not just incremented by
one...it's almost random.
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.
------------------------------------------------
------------------------------------------------
[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 MasterTable's columns
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). I
would also have it search the mastertable for the appropriate
Fundatakey. I don't know much about programming in excel but I do know
that a counter can not be used as Fundatakey is not just incremented by
one...it's almost random.
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.
------------------------------------------------
------------------------------------------------