How to transpose variable data from column A to multiple rows

Trying to learn excel as best I can but came across something Im not sure how to do, so hopefully I can get some help here.

I'm doing a mail merge and the data I pulled from our computer returned all the address data in one single column, but to do the merge you must have them in rows. Additionally, some addresses are more than 3 lines - some have as many as 6 - BUT everything is seperated by a blank row, so i think the formula could use that as a trigger to jump to the next line.

So currently my data looks like this:

Company A
1234 Main St.
Anytown, USA 12345
<Space>
Company B
Attn. Mr. Smith
1234 Main St.
Anytown, USA 12345
<Space>
Company C
1234 Main Street
Sometown
Australia
123456
<space>
etc...

And it needs to be

Company A / 1234 Main St. / Anytown, USA 12345
Company B / Attn. Mr. Smith / 1234 Main St. / Anytown, USA 12345
Company C / 1234 Main Street / Sometown / Australia / 123456


Does anyone have a formula or code code for this please? Im a VBA newb, but wanna learn. Thanks!!!

Hello Davidko19,

Welcome to the Forum!

Here is macro to convert the mail addresses from a single column into individual rows with multiple columns. This also sorts the addresses from A to Z order.

Please Login or Register  to view this content.

Adding the Macro

1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
   
7. Make any custom changes to the macro if needed at this time.
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Hello gjesus,

Welcome to the Forum!

You shouldn't post a new question in an existing post. You should start a new thread and add a link back to the post that pertains to your question. When you have time, please read the forum rules. There is link at the top of the page in the menu bar "Forum Rules".

Here is the code to transpose the data into columns of 12. The macro assumes the data starts in cell "A1" on "Sheet1". You can changes these to what you are using.

Please Login or Register  to view this content.

fubini89
Welcome to the Forum.

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Hi deepearl and welcome to the forum

Perhaps you missed the post directly above yours?

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.