name,address in same column different rows. How move to columns

[kimbafred]

I have a file that came out of a program where the Name, address,
citystatezip are in the same column but different rows. I have some 1400
names in this column and I need to get the address and citystatezip in
different rows so I can do a mail merge

 

[OssieMac]

I don't understand what you want to do. You say the data is in one column but
different rows and you want to get the address and citystatezip into
different rows. Can you post a sample of the existing data and a sample of
how you want them to look.

Regards,

OssieMac

 

[kimbafred]

For example

Column 1 row1: Mr. Jerry Brown
Column 1 row2: 123 Somewhere St.
Column 1 row3: Somewhere, CA 99999

I need to get the address line (Column 1 row2 and the City, State Zip line
(Column 1 row3 to go to different columns i.e.:
(Column 1row2 = (Column 2 row2 and (Column 1 row3 = (Column 3 row3
I don't even know if this is possible.

 

[OssieMac]

I am still not sure if I understand exactly what you want. The way I
interpret your description the result would finish up staggered like this:-

Mr. Jerry Brown
123 Somewhere St.
Somewhere, CA 99999

Is this what you want or do you want the name, address &citystatezip all on
the one row but in different columns like this:-

Mr. Jerry Brown 123 Somewhere St. Somewhere, CA 99999

I can provide you with a relatively simple macro to do what you want once I
understand what it required.


Regards,

OssieMac

 

[OssieMac]

Afterthought. Do you know how to insert a macro and run it or do I need to
provide instructions.

Also what version of XL do you have?

Regards,

OssieMac

 

[kimbafred]

OssieMac,
I do not know how to insert a macro or run it. Instructions would be grand!
I have Excell 2003.
Your first response hit the nail on the head. That is exactly what I am
trying to do. Any help would be greatly appreciated.

 

[kimbafred]

OssieMac,
The second choice is the one I am really wanting to do.

 

[OssieMac]

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.


Sub Process_Name_Address()

Dim rngList As Range

ActiveSheet.Copy Before:=Sheets(1)
Columns("B:G").ClearContents

Range("B1") = "Name"
Range("C1") = "Address"
Range("D1") = "CityStateZip"

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

For i = 1 To rngList.Count Step 3
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)
Next i

Columns("A:A").Delete
Columns("A:C").AutoFit

End Sub



Hope it works as you want.

Regards,

OssieMac

 

[kimbafred]

I will be back in the office tomorrow. I greatly appreciate all that you
have done. Look forward to trying.
Thanks a bunch!