moving data from rows to columns

[coolkidz33]

I am having an issue that I think goes beyond transpose. I have a database that basically goes like this:

Cells A1:A4
Business Name
Business Type
Contact 1
Phone

Cells B1:b4
Johnson Co
Retails
Jim Smith
212-555-5555

I am trying to get this information to move from cells A1:A4 to A1:D4 and then move the data to the proper rows. There are thousands of rows and after the data (Cells A5:A6 in this example) it might say: All Rights Reserved in A5 and Copyright 2011 in A6 and then start a new batch like A1:A4.

I have tried everything I can think of in Excel 2007 but to no avail. One issue is that some of the records may have Contact 1, Contact 2, Contact 3 and some don't so I need something that will leave blanks where there is no Contact 2.

I cannot get IF statements to work nor can I get index/match to work. I could not figure out how to post a little snippet of my spreadsheet to look at. Any help would be much appreciated. Thanks.

JD

 

[pbornemeier]

Please use Excel Jeanie (see link in my sig) to post a representative sample of your data as well as the the way the sample data should look after it is transformed.

I do not understand what you mean by "and then move the data to the proper rows. "
Is there another block of address data in A7:A12?

Do all address blocks end with the Copyright notice?

 

[coolkidz33]

Thanks Phil. I will try to do that tonight as I cannot install anything on my work computer.