Macros to append/merge records

[eyee]

Hi -

Fig. 1 below shows what I am trying create in Excel using a marcos. Rows that are populated in Columns A-D are primary records. Rows populated in Columns E-H are the secondary records appended to the primary. A row/space is added under the primary records starting with the 2nd secondary record.

Fig. 1[TABLE="width: 500"]
<TBODY>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[/TR]
[TR]
[TD]ID
[/TD]
[TD]EFFDT
[/TD]
[TD]ACTION
[/TD]
[TD]ANNUAL_RT
[/TD]
[TD]EFFDT
[/TD]
[TD]ACTION
[/TD]
[TD]COMPRATE
[/TD]
[TD]CD
[/TD]
[/TR]
[TR]
[TD]AX1234
[/TD]
[TD]01-01-10
[/TD]
[TD]PRO
[/TD]
[TD]200
[/TD]
[TD]01-01-10
[/TD]
[TD]PRO
[/TD]
[TD]10
[/TD]
[TD]FLXPCT
[/TD]
[/TR]
[TR]
[TD]AX1234
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]01-01-10
[/TD]
[TD]PRO
[/TD]
[TD]60
[/TD]
[TD]LUNCH
[/TD]
[/TR]
[TR]
[TD]AX1234
[/TD]
[TD]01-04-10
[/TD]
[TD]MER
[/TD]
[TD]300
[/TD]
[TD]01-04-10
[/TD]
[TD]MER
[/TD]
[TD]20
[/TD]
[TD]FLXPCT
[/TD]
[/TR]
[TR]
[TD]AX1234
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]01-04-10
[/TD]
[TD]MER
[/TD]
[TD]60
[/TD]
[TD]LUNCH
[/TD]
[/TR]
</TBODY>[/TABLE]


The data comes from a table where every row is a primary or secodary record, as per Fig. 2 below.

Fig. 2[TABLE="width: 500"]
<TBODY>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]ID
[/TD]
[TD]EFFDT
[/TD]
[TD]ACTION
[/TD]
[TD]ANNUAL_RT
[/TD]
[TD]CD
[/TD]
[/TR]
[TR]
[TD]AX1234
[/TD]
[TD]01-01-10
[/TD]
[TD]PRO
[/TD]
[TD]200
[/TD]
[TD]ANNUAL
[/TD]
[/TR]
[TR]
[TD]AX1234
[/TD]
[TD]01-01-10
[/TD]
[TD]PRO
[/TD]
[TD]10
[/TD]
[TD]FLXPCT
[/TD]
[/TR]
[TR]
[TD]AX1234
[/TD]
[TD]01-01-10
[/TD]
[TD]PRO
[/TD]
[TD]60
[/TD]
[TD]LUNCH
[/TD]
[/TR]
[TR]
[TD]AX1234
[/TD]
[TD]01-04-10
[/TD]
[TD]MER
[/TD]
[TD]300
[/TD]
[TD]ANNUAL
[/TD]
[/TR]
</TBODY>[/TABLE]


Every column B (EFFDT) change is a primary record. Secondary records need to be appended/merged to the side of the primary record like in the Fig. 1.
How then do I create a macros to append or merge the secondary records for each EFFDT change and insert the correct # of row/space below the primary records? Hopefully this all makes sense....

 

[cml19722000]

I might not have enough sample size to test the Macro.
Do let me know or provide bigger sample size - if help is needed.

Example: https://www.box.com/s/fw3uu6v517vkqxzxi8wh

 

[eyee]

Here is a bigger sample size.

https://www.box.com/s/exqqm87rfn1wtezmxzqh

 

[cml19722000]

Updated workbook: - https://www.box.com/s/33kam1zkdphiwlv42sa9