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?

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?

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?

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?

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?

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?