If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
name,address in same column different rows. How move to columns
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 |
#2
|
|||
|
|||
name,address in same column different rows. How move to columns
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" wrote: 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 |
#3
|
|||
|
|||
name,address in same column different rows. How move to column
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" wrote: 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" wrote: 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 |
#4
|
|||
|
|||
name,address in same column different rows. How move to column
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 "kimbafred" wrote: 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" wrote: 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" wrote: 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 |
#5
|
|||
|
|||
name,address in same column different rows. How move to column
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" wrote: 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" wrote: 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" wrote: 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 |
#6
|
|||
|
|||
name,address in same column different rows. How move to column
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. "OssieMac" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
#7
|
|||
|
|||
name,address in same column different rows. How move to column
OssieMac,
The second choice is the one I am really wanting to do. "OssieMac" wrote: 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 "kimbafred" wrote: 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" wrote: 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" wrote: 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 |
#8
|
|||
|
|||
name,address in same column different rows. How move to column
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 Befo=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" wrote: 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. "OssieMac" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
#9
|
|||
|
|||
name,address in same column different rows. How move to column
I will be back in the office tomorrow. I greatly appreciate all that you
have done. Look forward to trying. Thanks a bunch! "OssieMac" wrote: 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 Befo=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" wrote: 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. "OssieMac" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|