Converting rows to columns on a large scale

[Guest]

I have a workbook of names and addresses. All information is in column A...

John Doe
111 5th st.
New York, NY
Joe Smith
1st St. E
Chicago, IL

etc.

I want to convert this entire workbook to column form as follows,

John Doe 111 5th St New York, NY
Joe Smith 1st St. E Chicago, IL

Is there a way for me to do this without using "Paste Special" and
"Transpose" for each individual entry.....or tediously moving each entry one
by one?

bootsy

 

[Dave Peterson]

If the layout is always 3 rows per address, you can put this in B1 and copy to
the right. Then copy down (until you run out of data)

=INDEX($A:$A,MOD(COLUMN()-2,3)+1+(ROW()-1)*3)