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

[KMT_VBANewbie]

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:

Issue Age	Duration	Attained Age
18	1	18
18	2	19
18	3	20
18	4	21
18	5	22
18	6	23
18	7	24
18	8	25
18	9	26
18	10	27

<tbody>
</tbody>

TABLE 1, 10 YEAR TERM: BAND 1

Issue Age	M_Std	M-SubStd	F_Std	F_SubStd
18	2.08	2.94	1.79	2.28
19	2.10	3.00	1.81	2.33
20	2.13	3.09	1.83	2.39
21	2.17	3.21	1.86	2.46

<tbody>
</tbody>

TABLE 2, 10 YEAR TERM: BAND 2

Issue Age	M_Std	M-SubStd	F_Std	F_SubStd
18	1.93	2.72	1.68	2.13
19	1.95	2.77	1.70	2.18
20	1.98	2.85	1.72	2.23
21	2.02	2.96	1.75	2.29

<tbody>
</tbody>

TABLE 3: ATTAINED AGE RATES AFTER FIXED TERM PERIOD
ALL BANDS

Attained Age	M_Std	M-SubStd	F_Std	F_SubStd
28	3.12	5.46	1.80	2.85
29	3.09	5.43	1.89	3.03
30	3.06	5.40	1.98	3.21
31	3.03	5.43	2.10	3.45

<tbody>
</tbody>

SAMPLE OF WHAT FINAL TABLE SHOULD SHOW:

Attained Age	Issue Age	Sex	Band	Class	Duration	Rate
18	18	M	1	Std	10	2.08
28	18	M	1	Std	11	3.12
29	18	M	1	Std	12	3.09
30	18	M	1	Std	13	3.06
31	18	M	1	Std	14	3.03
18	18	M	1	SubStd	10	2.94
28	18	M	1	SubStd	11	5.46
29	18	M	1	SubStd	12	5.43
30	18	M	1	SubStd	13	5.40
31	18	M	1	SubStd	14	5.43
18	18	F	1	Std	10	1.79
28	18	F	1	Std	11	1.80
29	18	F	1	Std	12	1.89
30	18	F	1	Std	13	1.98
31	18	F	1	Std	14	2.10
18	18	F	1	SubStd	10	2.28
28	18	F	1	SubStd	11	2.85
29	18	F	1	SubStd	12	3.03
30	18	F	1	SubStd	13	3.21
31	18	F	1	SubStd	14	3.45
18	18	M	2	Std	10	1.93
28	18	M	2	Std	11	3.12
29	18	M	2	Std	12	3.09
30	18	M	2	Std	13	3.06
31	18	M	2	Std	14	3.03
18	18	M	2	SubStd	10	2.72
28	18	M	2	SubStd	11	5.46
29	18	M	2	SubStd	12	5.43
30	18	M	2	SubStd	13	5.40
31	18	M	2	SubStd	14	5.43
18	18	F	2	Std	10	1.68
28	18	F	2	Std	11	1.80
29	18	F	2	Std	12	1.89
30	18	F	2	Std	13	1.98
31	18	F	2	Std	14	2.10
18	18	F	2	SubStd	10	2.13
28	18	F	2	SubStd	11	2.85
29	18	F	2	SubStd	12	3.03
30	18	F	2	SubStd	13	3.21
31	18	F	2	SubStd	14	3.45

<tbody>
</tbody>

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

P.S. I apologize for any typos.