Need to copy information from one cell to another skipping every third row.

I am needing to create mailing labels from an excel file and do a mail merge in word. The problem is the way that the data copied from the program in our system is it copied the data to multiple rows. Here is how the data is set up.
Data.png
What I need is a macro to copy for example each address cell to column D. Then do the same for the city state and zip code for columns E, F, and G. The records are all in order every 3 rows. Normally I would do this manually but there are about 4500 rows in this excel worksheet. So for example I need to copy A2 to D1, then A5 to D2 and then so on and so forth all the way down to row 4613. I have done some research but can't find any formulas that I understand that can do this. Any help will be greatly appreciated.

Hello,

So first of all some notes:

1) Anything in the following with "*****" in the comments needs your attention, and I try to put a note as to how/why in the comment.
2) This assumes that all addresses have the same number of data points... as such it won't work if some have 3 lines, others have 5 lines, etc.
3) Make a new sheet for the target so you don't overwrite any data.

That said, there is a way to do this without a macro at all, which I will also show. First the code:

Please Login or Register  to view this content.

The formulas would just need to start in row 1 of the same sheet as the data, and you need to fill it over and change it slightly for each of the address parts. Once you do this for the first set of n address parts you can just fill the formula down and it will turn into zeroes when it runs out of data:

=OFFSET($A1,(ROW()-1)*5-IF(ROW()>1,ROW()-1,0),0)

Just change the bold part above to the A1-A5, and the 5 to represent 5 address parts. I have attached a jpg to show what I mean because I think the explanation is a little arcane.

Example.jpg

Cheers!
Xervice