Sorting an Address List

[steve_g]

I have a list of addresses I want to sort. It was exported from a
website, so it came into Excel this way:

Company Name1
Address1
City, State, Zip1
Phone1

Company Name2
Address2
City, State, Zip2
Phone2

And so on...

My goal is to get this list into colums where column A is Name, column
B is Address, column C is CityStateZip and ColumnD is Phone. This way
I can do a mail merge, or import it into Access.

I hope my example makes sense. Thanks for your help.

Steve

 

[N Harkawat]

If you data is always 4 rows of address then a blank row and then again 4
rows of next address and so on do the following:-

copy this address range from Cell F6 downwards

then on cell A2 type for company name
=INDEX($F$1:$F$5000,(ROW()*5)-4)

On cell B2 type for address1
=INDEX($F$1:$F$5000,(ROW()*5)-3)

On cell C2 for City,state
=INDEX($F$1:$F$5000,(ROW()*5)-2)

And finally on D2 for phone
=INDEX($F$1:$F$5000,(ROW()*5)-1)

and copy these 4 formulas all the way down.

This will give you your desired address list in columns that you can then
copy--> paste special--> values

to de-link them from the formulas and you could then easily sort

 

[LanceB]

assuming your list starts in A1 and is 4 lines with a blank line consistently

in b1
=OFFSET($A$1,((ROW()-1)*5),0)
in c1
=OFFSET($A$1,((ROW()-1)*5)+1,0)
in d1
=OFFSET($A$1,((ROW()-1)*5)+2,0)
in e1
=OFFSET($A$1,((ROW()-1)*5)+3,0)

and copy down

 

[steve_g]

That's exactly what I needed. Thanks for the help

 

[Alex]

Steve

Ok. Here is what to do. Get your data that is currently all in a column and
paste into a new worksheet starting at cell A1. (i.e so column A contains
your data)

Now for each address you have 4 rows i.e.

A1 Company Name1
A2 Address1
A3 City, State, Zip1
A4 Phone1

So let us suppose (as above) that A1 = Company Name1 A2 = Address1 A3= City,
State, Zip1 A4 = Phone 1

Then you have your next address starting in cell A5 or A6 (whatever - it
doesn't matter).

Ok, this is the tricky bit.

Go to cell B1 and highlight B1:E1 i.e 4 columns. Now type in the white
coloured cell...

=TRANSPOSE(A1:A4)

This bit is now crucial. DO NOT PRESS ENTER but instead press
CTRL+SHIFT+ENTER.

This will place you data in columns.

Now highlight cells B1:E1 and press CTRL + C (this is the shortcut for copy
but is much faster).

Now select the cell B5 or B6 i.e. wherever CompanyName2 is adjacent in
column A and press CTRL + V (i.e. paste shortcut). Keep on doing this for all
you address and this will place them all in columns.

Now to finish off, I would copy the entire range of inputs and then select

EDIT>PASTE SPECIAL>VALUES

This will make the values in columns no longer dependent upon column A
inputs. Now you are free to format the sheet as you like and do your mail
merge etc...

Do write back if you encounter problems...


Alex