Split Single Column Into Multiple Columns And Rows

[Steve_JR]

Hello all, I have a problem to which I am sure there must be a simple excel formula to solve. I have searched and still not found an answer to my problem. Sorry if the answer comes across as being very fundamental but somehow I have missed it. Here is my situation:

I have 300 rows worth of data that looks similar to this, all organized in one column:

John Q. Smith
Programmer
2111 NW 13th St
Anywhereville, USA, 55555
(555) 555-5555
Joe P. Snider
Organizer
5645 NW 45th St
Anywhereville, USA, 55555
(555) 555-5555
Patty Williams
Accountant
6454 NW 34th St
Anywhereville, USA, 55555
(555) 555-5555

As you can see it is consistent with the name, position, address line 1, address line 2, Phone number, for every single entry. All my names are already alphabetized so I don't have to worry about it. What I want to do is have the information for each entry translated into 5 separate columns so it looks like this.

John Q. Smith Programmer 2111 NW 13th St Anywhereville, USA, 55555 (555) 555-5555
Joe P. Small Organizer 5645 NW 45th St Anywhereville, USA, 55555 (555) 555-5555
Patty Williams Accountant 6454 NW 34th St Anywhereville, USA, 55555 (555) 555-5555

Supposing I started the sheet in the top left corner at A1, I was just going to have cell B1=A1 then Cell B2=A6 then just autofill down column B but it doesn't work. I have seen some people do something similar to what I want with VBA but I am convinced there has to be something simple in with an excel formula seeing as my information is already so organized and consistent.

Please help! Thanks! -Steve

[Dave Hawley]

This page on transposing with VBA should help

Transpose Rows Into Columns

[jindon]

try

B1:

=Indirect(Address((Row(A1)-1)*5+Column(A1),1))

Copy right + down

[Dave Hawley]

k.I.S.S I like it

[Steve_JR]

Thank you! -Steve

[wroomboom]

hey everyone,

i have the same problem, but very limited knowledge of excel, so i don't understand jindons help tip.

what does this mean? ->

B1:

=Indirect(Address((Row(A1)-1)*5+Column(A1),1))

Copy right + down


Can someone please explain the procedure step by step, for dummies like me?

Thanks a lot!