Hi, I'm new to this forum, and equally new to using macros in Excel 2007. I basically have three tables. The first two tables contains rates based

K

KMT_VBANewbie

New Member
Joined
Dec 3, 2013
Messages
1
Hi,

I'm new to this forum, and equally new to using macros in Excel 2007. I basically need to merge three tables into one.

The first two tables contains rates based on an issue age, and a fixed period of time. The only difference between them is the first table is for a "Band" class 1, the second table is "Band" class 2. The third table contains rates that are based on an attained age after the initial fixed period has expired, and applies regardless of the band class.

I need to make one individual table that will show the initial rate from the "Issue Age" tables, and that will then go to obtain the subsequent rates that will apply from the "Attained Age" table, and then go back to the "Issue Age" table and get the next initial rate, and then go back and fill in all of the subsequent rates from the "Attained Age" table....etc. And keep repeating this process until all of the rates have been extracted.

(My issue age tables usually go to around age 65; My attained age tables usually go to around age 100.)


I have a sample set shown below. I was thinking that I could accomplish this task by doing some lookup formulas, but I was hoping a macro could make this process quicker. I'd appreciate any and all suggestions on where to start, and how to accomplish this.

Thanks in advance!!!


For this scenario, this is how attained age is identified:
[TABLE="width: 300"]
<tbody>[TR]
[TD="align: center"]Issue Age[/TD]
[TD="align: center"]Duration[/TD]
[TD="align: center"]Attained Age[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]18[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]19[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]20[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]21[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]22[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]23[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]24[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]25[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]26[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]27[/TD]
[/TR]
</tbody>[/TABLE]


TABLE 1, 10 YEAR TERM: BAND 1
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Issue Age[/TD]
[TD="align: center"]M_Std[/TD]
[TD="align: center"]M-SubStd[/TD]
[TD="align: center"]F_Std[/TD]
[TD="align: center"]F_SubStd[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"]2.08[/TD]
[TD="align: center"]2.94[/TD]
[TD="align: center"]1.79[/TD]
[TD="align: center"]2.28[/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD="align: center"]2.10[/TD]
[TD="align: center"]3.00[/TD]
[TD="align: center"]1.81[/TD]
[TD="align: center"]2.33[/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD="align: center"]2.13[/TD]
[TD="align: center"]3.09[/TD]
[TD="align: center"]1.83[/TD]
[TD="align: center"]2.39[/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD="align: center"]2.17[/TD]
[TD="align: center"]3.21[/TD]
[TD="align: center"]1.86[/TD]
[TD="align: center"]2.46[/TD]
[/TR]
</tbody>[/TABLE]

TABLE 2, 10 YEAR TERM: BAND 2[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Issue Age[/TD]
[TD="align: center"]M_Std[/TD]
[TD="align: center"]M-SubStd[/TD]
[TD="align: center"]F_Std[/TD]
[TD="align: center"]F_SubStd[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"]1.93[/TD]
[TD="align: center"]2.72[/TD]
[TD="align: center"]1.68[/TD]
[TD="align: center"]2.13[/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD="align: center"]1.95[/TD]
[TD="align: center"]2.77[/TD]
[TD="align: center"]1.70[/TD]
[TD="align: center"]2.18[/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD="align: center"]1.98[/TD]
[TD="align: center"]2.85[/TD]
[TD="align: center"]1.72[/TD]
[TD="align: center"]2.23[/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD="align: center"]2.02[/TD]
[TD="align: center"]2.96[/TD]
[TD="align: center"]1.75[/TD]
[TD="align: center"]2.29[/TD]
[/TR]
</tbody>[/TABLE]


TABLE 3: ATTAINED AGE RATES AFTER FIXED TERM PERIOD
ALL BANDS
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Attained Age[/TD]
[TD="align: center"]M_Std[/TD]
[TD="align: center"]M-SubStd[/TD]
[TD="align: center"]F_Std[/TD]
[TD="align: center"]F_SubStd[/TD]
[/TR]
[TR]
[TD="align: center"]28[/TD]
[TD="align: center"]3.12[/TD]
[TD="align: center"]5.46[/TD]
[TD="align: center"]1.80[/TD]
[TD="align: center"]2.85[/TD]
[/TR]
[TR]
[TD="align: center"]29[/TD]
[TD="align: center"]3.09[/TD]
[TD="align: center"]5.43[/TD]
[TD="align: center"]1.89[/TD]
[TD="align: center"]3.03[/TD]
[/TR]
[TR]
[TD="align: center"]30[/TD]
[TD="align: center"]3.06[/TD]
[TD="align: center"]5.40[/TD]
[TD="align: center"]1.98[/TD]
[TD="align: center"]3.21[/TD]
[/TR]
[TR]
[TD="align: center"]31[/TD]
[TD="align: center"]3.03[/TD]
[TD="align: center"]5.43[/TD]
[TD="align: center"]2.10[/TD]
[TD="align: center"]3.45[/TD]
[/TR]
</tbody>[/TABLE]


SAMPLE OF WHAT FINAL TABLE SHOULD SHOW:

[TABLE="width: 486"]
<tbody>[TR]
[TD="align: center"]Attained Age[/TD]
[TD="align: center"]Issue Age[/TD]
[TD="align: center"]Sex[/TD]
[TD="align: center"]Band[/TD]
[TD="align: center"]Class[/TD]
[TD="align: center"]Duration[/TD]
[TD="align: center"]Rate[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Std[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]2.08[/TD]
[/TR]
[TR]
[TD="align: center"]28[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Std[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]3.12[/TD]
[/TR]
[TR]
[TD="align: center"]29[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Std[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]3.09[/TD]
[/TR]
[TR]
[TD="align: center"]30[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Std[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]3.06[/TD]
[/TR]
[TR]
[TD="align: center"]31[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Std[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]3.03[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]SubStd[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]2.94[/TD]
[/TR]
[TR]
[TD="align: center"]28[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]SubStd[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]5.46[/TD]
[/TR]
[TR]
[TD="align: center"]29[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]SubStd[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]5.43[/TD]
[/TR]
[TR]
[TD="align: center"]30[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]SubStd[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]5.40[/TD]
[/TR]
[TR]
[TD="align: center"]31[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]SubStd[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]5.43[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Std[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]1.79[/TD]
[/TR]
[TR]
[TD="align: center"]28[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Std[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]1.80[/TD]
[/TR]
[TR]
[TD="align: center"]29[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Std[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]1.89[/TD]
[/TR]
[TR]
[TD="align: center"]30[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Std[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]1.98[/TD]
[/TR]
[TR]
[TD="align: center"]31[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Std[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]2.10[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]SubStd[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]2.28[/TD]
[/TR]
[TR]
[TD="align: center"]28[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]SubStd[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]2.85[/TD]
[/TR]
[TR]
[TD="align: center"]29[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]SubStd[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]3.03[/TD]
[/TR]
[TR]
[TD="align: center"]30[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]SubStd[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]3.21[/TD]
[/TR]
[TR]
[TD="align: center"]31[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]SubStd[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]3.45[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Std[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]1.93[/TD]
[/TR]
[TR]
[TD="align: center"]28[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Std[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]3.12[/TD]
[/TR]
[TR]
[TD="align: center"]29[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Std[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]3.09[/TD]
[/TR]
[TR]
[TD="align: center"]30[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Std[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]3.06[/TD]
[/TR]
[TR]
[TD="align: center"]31[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Std[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]3.03[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]SubStd[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]2.72[/TD]
[/TR]
[TR]
[TD="align: center"]28[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]SubStd[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]5.46[/TD]
[/TR]
[TR]
[TD="align: center"]29[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]SubStd[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]5.43[/TD]
[/TR]
[TR]
[TD="align: center"]30[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]SubStd[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]5.40[/TD]
[/TR]
[TR]
[TD="align: center"]31[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]SubStd[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]5.43[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Std[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]1.68[/TD]
[/TR]
[TR]
[TD="align: center"]28[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Std[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]1.80[/TD]
[/TR]
[TR]
[TD="align: center"]29[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Std[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]1.89[/TD]
[/TR]
[TR]
[TD="align: center"]30[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Std[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]1.98[/TD]
[/TR]
[TR]
[TD="align: center"]31[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Std[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]2.10[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]SubStd[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]2.13[/TD]
[/TR]
[TR]
[TD="align: center"]28[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]SubStd[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]2.85[/TD]
[/TR]
[TR]
[TD="align: center"]29[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]SubStd[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]3.03[/TD]
[/TR]
[TR]
[TD="align: center"]30[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]SubStd[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]3.21[/TD]
[/TR]
[TR]
[TD="align: center"]31[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]SubStd[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]3.45[/TD]
[/TR]
</tbody>[/TABLE]

(My table would then go to issue age 19, and repeat the process.)

P.S. I apologize for any typos. ;)
 
You must log in or register to reply here.

Similar threads

D
  • Question Question
Copy/Pasting rows of a table to specific cells of a separate worksheet template and exporting as a new file.
Replies
0
Views
256
DjentChicken
D
P
  • Solved
Lookup based on multiple criteria
2 3
Replies
22
Views
1K
paulkc
P
N
  • Solved
Copy specific cells based on controls checkbox and paste in the next empty row on a table
Replies
5
Views
215
nstaab07
N
H
  • Solved
Creating pivot table from two sources - I'm not sure how to use data from one of the tables
Replies
3
Views
231
hassan44
H
JenniferMurphy
  • Solved
Is there a way to sort a table without screwing up a scatter chart based on that table?
Replies
9
Views
1K
JenniferMurphy
JenniferMurphy

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top