Right now the data looks like this:
COLUMN1 COLUMN2 COLUMN3 40 Broad Street Homeland Mechanical, LLC Owner Quincy, MA 02169 [blank] Contact: Edward Sweeny [blank] Phone: (617) 845-5334 Alt Phone: (508) 901-0852 Fax: ***-**** [blank] 1311 Eastern Avenue Better Comfort Systems, Inc. President Malden, MA 02148 [blank] Contact: James Shaviro [blank] Phone: (781) 322-4099 Fax: ***-**** [blank]
The number of rows of data that each company takes up varies for a number of reasons. For example, the number of rows of the postal address can be more than two, e.g. if there is a P.O. box or apartment number. Also, as in the example above, companies may or may not have an "Alt Phone." So, the number of blank cells between cells with information in Column2 varies between seven and ten.
Need to rearrange to:
Column4 Column5 Column6 Column7 Business name Contact Person Street address City and state Homeland Mechanical, LLC Owner Edward Sweeny 40 Broad Street Quincy, MA 02169 Better Comfort Systems, Inc. President James Shaviro 1311 Eastern Avenue Malden, MA 02148