Right now the data looks like this:

COLUMN1                          COLUMN2                             COLUMN3

40 Broad Street                  Homeland Mechanical, LLC            Owner
Quincy, MA 02169
Contact: Edward Sweeny
Phone: (617) 845-5334
Alt Phone: (508) 901-0852
Fax: ***-****
1311 Eastern Avenue              Better Comfort Systems, Inc.        President
Malden, MA 02148
Contact: James Shaviro
Phone: (781) 322-4099
Fax: ***-****

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 
  • 1
    You should not post real contact data. – Twisty Impersonator Apr 23 '17 at 19:25
  • the data is modified from the real contact data – confucius_did_shrooms Apr 23 '17 at 22:52
  • What have you tried, or are you expecting someone to write the code for you? Also, you show no examples of the output for some of your cases (eg. alt phone, multi-line addresses, etc). I would go through line-by-line and extract the info as you go. Maybe a collection of class objects, where the object is the Company and the properties are each of the items. You can tell where a company's data starts by looking at Column 2. And everything is labelled except for the Address and City/state/zip lines. – Ron Rosenfeld Apr 27 '17 at 18:05
  • Good comment, Ron…….for me to poop on – confucius_did_shrooms Apr 29 '17 at 16:34

This will not completely solve your issue as it seems there is a different number of rows between each set of data, but a good start would be to select all cells from A1 to the bottom-right cell, copy and then Paste Special... Transpose.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.