Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Convert address list

  1. #1
    New Member
    Join Date
    May 2002
    Location
    Atlanta, Ga
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a list of addresses that runs in a column like a normal postal address (5 rows). I'm trying to convert it to one address to a row instead of forty or fifty in one column. When I use the transpose it puts the entire column on on row. How can I get it to copy the addresses and put each address on a seperate row?

    Thanks for your help,
    Bill

  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Okay, it may not work exactly, but you should get the idea. Suppose you have 5 lines per address, and one line in between (and no headings):

    B1: =A1
    C1: =A2
    D1: =A3
    E1: =A4
    F1: =A5

    B7: =A7
    C7: =A8
    D7: =A9
    E7: =A10
    F7: =A11

    Now, select cells B1 through F12.
    Grab the fill handle and copy down as far as you need.
    Don't let go of the selected area.
    Hit Copy.
    Hit Edit-Paste special, Values.
    Delete column A.
    Select the new column A.
    Hit Edit-Go to-Special, Blanks.
    Hit Edit-Delete, choose Entire row.

    Hope that helps!

    _________________
    TheWordExpert

    [ This Message was edited by: Dreamboat on 2002-05-12 13:14 ]

  3. #3
    New Member
    Join Date
    May 2002
    Location
    New Jersey
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This is a great technique. Only one correction, Dreamboat. You wrote:
    "Now, select cells B1 through B12.
    Grab the fill handle and copy down as far as you need. "
    I tried it, and discovered it should read "B1 through F12." (Thank the stars for the Undo button...)

  4. #4
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here's a VB solution that will loop until a blank row is encountered.
    Code:
    Sub TransAddr()
    ' Transpose Addr until blank row is found
        Do
        RowCnt = RowCnt + 1
            For ColCnt = 1 To 5
                Cells(RowCnt, ColCnt).Value = Cells(RowCnt + ColCnt - 1, 1).Value
                If ColCnt = 5 Then Rows(RowCnt + 1 & ":" & RowCnt + 4).Delete Shift:=xlUp
            Next
        Loop Until Len(Trim(Cells(RowCnt, 1).Value)) = 0
    End Sub

  5. #5
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Okay, thanks!! I made that correction to make it easier for people NOT to make the same mistake.


    ~Anne Troy

  6. #6
    New Member
    Join Date
    May 2002
    Location
    Atlanta, Ga
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Maybe I need to explain again. Here goes; All the addresses are in column A.

    A1 Company
    A2 Street address
    A3 City
    A4 Phone
    A5 Blank row

    What I'm looking for is this.

    A1 Company
    B1 Street Address
    C1 City
    D1 Phone

    The transpose command does what I want but I have fifty or so Addresses. If I take the column and transpose it all then it puts it all on row 1. How can I do this with out haveing to select each address and tranpose it one at a time?



  7. #7
    New Member
    Join Date
    May 2002
    Location
    Atlanta, Ga
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    VB Script worked fine after a few test runs. Thanks

  8. #8
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-13 06:54, Billmania wrote:
    Maybe I need to explain again. Here goes; All the addresses are in column A.

    A1 Company
    A2 Street address
    A3 City
    A4 Phone
    A5 Blank row

    What I'm looking for is this.

    A1 Company
    B1 Street Address
    C1 City
    D1 Phone

    The transpose command does what I want but I have fifty or so Addresses. If I take the column and transpose it all then it puts it all on row 1. How can I do this with out haveing to select each address and tranpose it one at a time?
    Hi Billmania:
    Following is one of the ways you can use the Transpose function for your situation.

    In cell C1, put the following formula
    =(("A"&(ROW()-1)*5+1&":A"&(ROW()*5-1)))
    and copy this down from c2:c50 to take care of all of your 50 records.

    Then in cell D1 write the following formula:
    =Transpose(indirect(C1)) -- this is an array formula, clcick on cell D1, highlite cells D1:G1 and array enter the formula in cell D1
    this will transpose the entries in A1:A4 to D1:G1
    Now copy the formula in cell D1 to D2:D50 and all your 50 records will be now 1-liners in columns D through G.

    Please post back if it works for you .. otherwise explain a little further and let us take it from there.

    Regards!




    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  9. #9
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You can also do it by without using the Traspose function by writing the following formulas:

    in cell D1 ... '=INDIRECT("A"&(ROW()-1)*5+1)
    in cell E1 ... '=INDIRECT("A"&(ROW()-1)*5+2)
    in cell F1 ... '=INDIRECT("A"&(ROW()-1)*5+3)
    in cell G1 ... '=INDIRECT("A"&(ROW()-1)*5+4)

    Then copy the formulas in cells D1:G1 to D2:D50 and you will have all of your fifty records from column A as 1-liners in cells D1:G50

    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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