+ Reply to Thread
Results 1 to 4 of 4

Address labels to columns

  1. #1
    Lady Layla
    Guest

    Address labels to columns

    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!




  2. #2
    Otto Moehrbach
    Guest

    Re: Address labels to columns

    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 [email protected]. Remove the "opq" from
    this address. HTH Otto

    "Lady Layla" <[email protected]> wrote in message
    news:[email protected]...
    >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!
    >
    >
    >




  3. #3
    Jason Morin
    Guest

    Re: Address labels to columns

    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
    >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!
    >
    >
    >
    >.
    >


  4. #4
    Gord Dibben
    Guest

    Re: Address labels to columns

    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

    On Fri, 18 Feb 2005 10:23:44 -0500, "Lady Layla" <[email protected]> wrote:

    >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!
    >
    >



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1