Convert address list

[Billmania]

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

 

[Anne Troy]

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

 

[CompTutor]

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

 

[Nimrod]

Here's a VB solution that will loop until a blank row is encountered.

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

 

[Anne Troy]

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

 

[Billmania]

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?

 

[Billmania]

VB Script worked fine after a few test runs. Thanks

 

[Yogi Anand]

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!

 

[Yogi Anand]

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