New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Arrange Address Information in Excel Using a Macro

Macros, VBA, Excel Automation, etc.

Arrange Address Information in Excel Using a Macro

Postby Luigi » Mon Dec 12, 2011 11:22 am

I am trying to import addresses from a database in my server so that i can send letters in a mail merge. I have been able to take the information from a "labels" document and import it into excel. Now that i have the information i need to have it in appropriate columns so that i can sort and run the merge. The imorted names and addresses are not in columns. (For example in A1 i have Bob Smith, in A2 Bob's address, in A3 Bob's city, state, and zip. Then row 4 is blank all the way across. The next client is shown in A5 as Joe Shmoe, then A6,Joe's address, A7 JOe's city,state, and zip.)Can you give me a way to get this information into appropriate columns (First and last name, address City, State, Zip) so that i can send letters to these clients in a mail merge.
  • 0

Luigi
Rookie
 
Posts: 4
Joined: Dec 12, 2011
Reputation: 0

Re: mailings

Postby Don » Mon Dec 12, 2011 12:11 pm

Hi Luigi!

Check out these links:


http://office.microsoft.com/en-us/excel-help/create-and-print-mailing-labels-for-an-address-list-in-excel-HP010243267.aspx

http://support.microsoft.com/kb/318117


The links will provide you will step-by-step information on how to perform the mail-merge and get the information in the desired format/order. Basically, the process of performing the mail-merge will also arrange the addresses into the desired format.
  • 0

Don
Moderator
 
Posts: 733
Joined: Dec 4, 2011
Reputation: 2
Excel Version: 2010

Re: mailings

Postby Luigi » Mon Dec 12, 2011 1:26 pm

thank you Don, but I already know how to run the mailmerge. The problem is that all the data for my mailing is in one column like this...

NAME
ADDRESS
CITY,STATE,ZIP
NAME
ADDRESS
CITY,STATE,ZIP
NAME
ADDRESS
CITY,STATE,ZIP

I want to get the Name in column A, the addresses in column B, and the city, state and zip in their respective columns. How do i move the data?
  • 0

Luigi
Rookie
 
Posts: 4
Joined: Dec 12, 2011
Reputation: 0

Re: mailings

Postby Don » Mon Dec 12, 2011 3:01 pm

*edited post title to reflect your question and move to the programming forum

Ok, well then you should use a macro for this.

Try this one:

Code: Select all
Sub test()
   
lastrow = Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To lastrow Step 3

    Cells(i, 2).Value = Cells(i + 1, 1).Value
    Cells(i, 3).Value = Cells(i + 2, 1).Value
   
    Cells(i + 1, 1).Value = ""
    Cells(i + 2, 1).Value = ""

Next i
   
End Sub


This assumes your data starts in row 1 and is exactly like you mentioned in your previous post. After you run the macro, just sort the data to get rid of the empty rows and use Text-to-Columns on column C if you want to put the City, State, and Zip into separate columns.
  • 0

Don
Moderator
 
Posts: 733
Joined: Dec 4, 2011
Reputation: 2
Excel Version: 2010


Return to Macros and VBA Questions

Who is online

Users browsing this forum: Google [Bot] and 31 guests

cron