I have a very lengthy, horizontal list of names and addresses in Word that
need to become a sorted Excel spreadsheet ready for a mail merge. I'm
looking for shortcuts that won't involve many hours of drag and drop or cut
and paste.

In other words, instead of

Contact Name
Title
Company
Address
City, State Zip

all one on top of the other, I need to have Column A be a list of the names,
Column B will be all the titles, etc. So I need a macro that not only cuts
and pastes, but puts the next one I select on the next line of the list.
Sort of "Move to F4+1, Move to F4+2, Move to F4+3" or something like that. I
hope this is clear.

Hi Allen,

I wrote code for this for someone recently on this forum. I have adjusted it
to accommodate your number of lines in each address.

I dont know how familiar you are with VBA but just in case Ill include
instructions for you to get started.

I suggest that you close any applications you are not using and only have
Excel and your internet open with this posting so that you do not get
confused about which window you are working with.

WARNING:- Create a backup copy of your workbook in case something goes wrong.

Your data is to be in column A only. If not, you need to place it in column A.

If you have a column header then delete the entire row to remove it so the
data starts at cell A1.

Alt/F11 to open VBA Editor.

Click on menu item Insert then Module.

Copy the macro from this posting into the VBA editor. (The large white blank
area on the right after you inserted the module.). Ensure that you copy only
the macro and that you get it all from Sub Process_Name_Address() to End Sub.

There is a comment which is in green telling you to adjust the range of data
in the following command line which is in black print to match the range
which you have. You should only have to change the 999 to match the last row
of your data.

Change windows back to the Excel Worksheet. (Ensure that you are on the
worksheet with the raw data because the macro runs on the active sheet.)

Select Tools-Macro-Macros-Process_Name_Address-Run

The Macro will copy the worksheet to a new one so that your original data is
not destroyed if the macro does not do what you want it to. If it is not
right, you can simply delete the processed sheet and go back to the original
sheet with your original data still intact. new Sheet is named 'Output Data'


Sub Process_Name_Address()

Dim rngList As Range

'Create new sheet for output.
ActiveSheet.Copy Befo=Sheets(1)
ActiveSheet.Name = "Output Data"

Columns("B:F").ClearContents

Range("B1") = "Contact Name"
Range("C1") = "Title"
Range("D1") = "Company"
Range("E1") = "Address"
Range("F1") = "City, State Zip"

'Adjust the following range A1:A999 to suit your range
Set rngList = Range("A1:A999")

For i = 1 To rngList.Count Step 5
Cells(Rows.Count, 2).End(xlUp).Offset(1, 0) _
= Cells(i, 1)
Cells(Rows.Count, 3).End(xlUp).Offset(1, 0) _
= Cells(i + 1, 1)
Cells(Rows.Count, 4).End(xlUp).Offset(1, 0) _
= Cells(i + 2, 1)
Cells(Rows.Count, 5).End(xlUp).Offset(1, 0) _
= Cells(i + 3, 1)
Cells(Rows.Count, 6).End(xlUp).Offset(1, 0) _
= Cells(i + 4, 1)

Next i

Columns("A:A").Delete
Columns("A:F").AutoFit
Rows("1:1").Font.Bold = True


End Sub

Regards,

OssieMac


"AllenRubinstein" wrote:

I have a very lengthy, horizontal list of names and addresses in Word that
need to become a sorted Excel spreadsheet ready for a mail merge. I'm
looking for shortcuts that won't involve many hours of drag and drop or cut
and paste.

In other words, instead of

Contact Name
Title
Company
Address
City, State Zip

all one on top of the other, I need to have Column A be a list of the names,
Column B will be all the titles, etc. So I need a macro that not only cuts
and pastes, but puts the next one I select on the next line of the list.
Sort of "Move to F4+1, Move to F4+2, Move to F4+3" or something like that. I
hope this is clear.