Populate Column with Row Data

I am receiving data in a report that looks like this:

PHP Code: 

Recipient        500420    NORTHERN AFFAIRS CDA
Agreement        QC0400081    DSPNI 
Amd    Initiated    Status    Recommendation
1    2004.11.17    In Progress    Approved
2    2005.03.02    In Progress    Approved
3    2005.11.19    In Progress    Approved
4    2006.02.23    In Progress    Approved
5    2006.12.27    In Progress    Approved
6    2007.11.28    In Progress    Approved
7    2008.02.06    In Progress    Approved
Recipient        500428    GAZETTE 
Agreement        QC0900062    EFCPN 
Amd    Initiated    Status    Recommendation
1    2004.10.13    In Progress    Approved
2    2005.02.23    In Progress    Approved 


I would like to pull the agreement number and add it as a column for each amendment. I would also like to delete the 3 header lines. So at the end my data would look like this:

PHP Code: 

QC0400061    1    2004.11.17    In Progress    Approved
QC0400081    2    2005.03.02    In Progress    Approved
QC0400081    3    2005.11.19    In Progress    Approved
QC0400081    4    2006.02.23    In Progress    Approved
QC0400081    5    2006.12.27    In Progress    Approved
QC0400081    6    2007.11.28    In Progress    Approved
QC0400081    7    2008.02.06    In Progress    Approved
QC0900062    1    2004.10.13    In Progress    Approved
QC0900062    2    2005.02.23    In Progress    Approved 


My agreement number is always in column "C" or will be in "D" if I add a new column. I seem to need to create Ranges that go from Row Recipient to Next Row Recipient, Populate "A" with Row + 1 "D" from Row Recipient + 3 to Row Next Recipent - 1. Then delete Row Recipient, Row Recipient + 1, Row Recipient + 2. Then Row Next Recipient becomes Row Recipient and I go find Row Next Recipient and start again till I reach end.

Atleast that's what I think I need, but how to get write it is a little beyond my very basic VBA skills.

Thanks in advance for any advice and help.

Put this macro in a standard module and run it on a copy of your data, I think it does what you'd like. The macro is designed to run from A1 to the first EMPTY cell in column A and stop.

Please Login or Register  to view this content.

Now that I've seen your data, the only thing I would add is ONE more line of code at the bottom:

Please Login or Register  to view this content.

Clean up the results appearance a little.