Address labels to columns

[Lady Layla]

I have an employee who has saved a mailing list as the actual address labels
within Word. We have pulled this listing over to Excel wanting to change it
from the current layout to a more practical database style listing

The format is currently

Name
Company
Address
City, State Zip

We would like to transpose each "label" into the following format without having
to physically cut an paste.

Name Company Address City State Zip

Temporary solutions we have started trying is to use the Array Forumla
Transpose. This at leasts pulls 1 label into a row, but is there a macro or
other way to do this more efficiently?

I know I have seen something similar here but can not find it.


Thanks!

 

[Otto Moehrbach]

To do this repeatedly you will need VBA. The actual code is straight
forward but the details of the code depend on the details of your data
layout. For instance, is there a blank row between listings? Or is the
number of rows per listing always the same?
If you wish, send me direct a file with a sample of what you have to
start with, as well as a few rows of what you want to have as the end
product. My email address is (e-mail address removed). Remove the "opq" from
this address. HTH Otto

 

[Jason Morin]

You can use OFFSET, but it depends on the exact layout of
your data. Are there breaks between labels? Is the
city/state/zip in one cell or 3?

Take a look at some examples through a google search
using keywords "OFFSET" and "city". Here's one example:

http://tinyurl.com/5fffh

HTH
Jason
Atlanta, GA

-----Original Message-----
I have an employee who has saved a mailing list as the actual address labels
within Word. We have pulled this listing over to Excel wanting to change it
from the current layout to a more practical database style listing

The format is currently

Name
Company
Address
City, State Zip

We would like to transpose each "label" into the

following format without having

 

[Gord Dibben]

Layla

If the data is consistent......4 rows per set.....try this macro.

Sub ColtoRows_NoError()
Dim Rng As Range
Dim i As Long
Dim j As Long
Dim nocols As Integer
Application.ScreenUpdating = False
Set Rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
On Error Resume Next
nocols = InputBox("Enter Number of Columns Desired")
For i = 1 To Rng.Row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(i, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(Rng.Row, "A")).ClearContents
Application.ScreenUpdating = True
End Sub


Gord Dibben Excel MVP