Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Address list
I have a list of names and addresses that are listed in rows vs. columns, is
there a way to convert the information to columns so I can do a mail merge for address labels? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Address list
Transpose?
-- David Biddulph kcurland wrote: I have a list of names and addresses that are listed in rows vs. columns, is there a way to convert the information to columns so I can do a mail merge for address labels? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Address list
You can try using Copy, followed by Edit Paste Special with the 'Transpose'
option selected. But this is going to require you to select each address group separately. A better solution would be to use a macro, but before going into that we'd need to know the layout of your addresses: Are they always 3 rows long, 4? Some 3, some 4, some other? What column(s) are they in, and what's the first row with the start of address information in it? In other words, the details of how your address information is currently laid out on your worksheet. "kcurland" wrote: I have a list of names and addresses that are listed in rows vs. columns, is there a way to convert the information to columns so I can do a mail merge for address labels? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Address list
They are all 3 rows and are all in column "A", see below.
James Adams 1076 Oak Hollow Lane Combine, TX 75159 "JLatham" wrote: You can try using Copy, followed by Edit Paste Special with the 'Transpose' option selected. But this is going to require you to select each address group separately. A better solution would be to use a macro, but before going into that we'd need to know the layout of your addresses: Are they always 3 rows long, 4? Some 3, some 4, some other? What column(s) are they in, and what's the first row with the start of address information in it? In other words, the details of how your address information is currently laid out on your worksheet. "kcurland" wrote: I have a list of names and addresses that are listed in rows vs. columns, is there a way to convert the information to columns so I can do a mail merge for address labels? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Address list
This should do it for you, I think all you need to change will be the two
sheet names involved. Open your workbook, make sure there's an empty sheet in it somewhere to receive the transposed list. Press [Alt]+[F11] to open the VB Editor and in it, choose Insert -- Module and copy the code below and paste it into the module. Edit the code and close the VB editor. Run it from Tools -- Macros -- Macro and choose its name and click the [Run] button. Sub TransposeAddressList() 'change the Const values to 'agree with the sheet names 'in your workbook 'the name of the sheet with the 'current list of names on it Const sourceSheetName = "Sheet1" Const firstNameRow = 1 'number of entries per address Const groupSize = 3 'name of an empty sheet available 'to receive the transposed list Const destSheetName = "Sheet2" Dim srcWS As Worksheet Dim destWS As Worksheet Dim lastRow As Long Dim LC As Long Set srcWS = ThisWorkbook.Worksheets(sourceSheetName) lastRow = srcWS.Range("A" & Rows.Count).End(xlUp).Row Set destWS = ThisWorkbook.Worksheets(destSheetName) For LC = 1 To lastRow Step groupSize destWS.Range("A" & Rows.Count). _ End(xlUp).Offset(1, 0) = srcWS.Range("A" & LC) destWS.Range("A" & Rows.Count). _ End(xlUp).Offset(0, 1) = srcWS.Range("A" & LC + 1) destWS.Range("A" & Rows.Count). _ End(xlUp).Offset(0, 2) = srcWS.Range("A" & LC + 2) Next Set srcWS = Nothing Set destWS = Nothing MsgBox "Job Finished", vbOKOnly, "Work Completed" End Sub "kcurland" wrote: They are all 3 rows and are all in column "A", see below. James Adams 1076 Oak Hollow Lane Combine, TX 75159 "JLatham" wrote: You can try using Copy, followed by Edit Paste Special with the 'Transpose' option selected. But this is going to require you to select each address group separately. A better solution would be to use a macro, but before going into that we'd need to know the layout of your addresses: Are they always 3 rows long, 4? Some 3, some 4, some other? What column(s) are they in, and what's the first row with the start of address information in it? In other words, the details of how your address information is currently laid out on your worksheet. "kcurland" wrote: I have a list of names and addresses that are listed in rows vs. columns, is there a way to convert the information to columns so I can do a mail merge for address labels? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Address list
I don't have much of experience in Mail-Merging but I would suggest that you
check this out. If the Fields should be Text and not Formulas - you can copy and PasteSpecial, to another Sheet region, by selecting "values". http://img696.imageshack.us/img696/4892/nonamem.png Micky "kcurland" wrote: I have a list of names and addresses that are listed in rows vs. columns, is there a way to convert the information to columns so I can do a mail merge for address labels? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can an address list be alphabetized in excel? | Charts and Charting in Excel | |||
how do you de-dup an address list in Excel? | Excel Worksheet Functions | |||
excel list of names, addresses and email to address book/contact list??? | Excel Discussion (Misc queries) | |||
Address List in Excel | Excel Discussion (Misc queries) | |||
long address list, name-address-city, listed vertically, how do y. | Excel Discussion (Misc queries) |