Coverting information in rows to colums

Information is in one column many rows.
a1 1234
a2 henry
a3 jones
a4 43 melrose st
a5 wallyvile
need to transpose this information into seperate colums
example column a 1234 column b henry column c jones etc
this spreadsheet has over 300 address

See the instructions for Vertical to Horizontal Addresses at:
http://www.officearticles.com/excel/...soft_excel.htm

*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"bbc1" <[email protected]> wrote in message
news:[email protected]...
> Information is in one column many rows.
> a1 1234
> a2 henry
> a3 jones
> a4 43 melrose st
> a5 wallyvile
> need to transpose this information into seperate colums
> example column a 1234 column b henry column c jones etc
> this spreadsheet has over 300 address
>

Always 5 rows per address and no blank cells between addresses?

A1:A5, A6:A10, A11:A15, ...

If yes, put this in B1:
=INDEX($A:$A,(ROW()-1)*5+COLUMN()-1)
Drag to F1.

Select B1:F1 and drag down until you see the formulas start returning 0's
(you've run out of data). (Then clean up those 0's.)

If you want,
Select B:F
edit|copy
edit|paste special|Values

Delete column A.

If you have varying amount of rows per address or gaps between addresses, then
this won't work.


bbc1 wrote:
>
> Information is in one column many rows.
> a1 1234
> a2 henry
> a3 jones
> a4 43 melrose st
> a5 wallyvile
> need to transpose this information into seperate colums
> example column a 1234 column b henry column c jones etc
> this spreadsheet has over 300 address

--

Dave Peterson

Thank you this worked

"Dave Peterson" wrote:

> Always 5 rows per address and no blank cells between addresses?
>
> A1:A5, A6:A10, A11:A15, ...
>
> If yes, put this in B1:
> =INDEX($A:$A,(ROW()-1)*5+COLUMN()-1)
> Drag to F1.
>
> Select B1:F1 and drag down until you see the formulas start returning 0's
> (you've run out of data). (Then clean up those 0's.)
>
> If you want,
> Select B:F
> edit|copy
> edit|paste special|Values
>
> Delete column A.
>
> If you have varying amount of rows per address or gaps between addresses, then
> this won't work.
>
>
> bbc1 wrote:
> >
> > Information is in one column many rows.
> > a1 1234
> > a2 henry
> > a3 jones
> > a4 43 melrose st
> > a5 wallyvile
> > need to transpose this information into seperate colums
> > example column a 1234 column b henry column c jones etc
> > this spreadsheet has over 300 address
>
> --
>
> Dave Peterson
>