transposing 4 rows from 1 column to 4 columns w/o using a macro

[jlajoie]

I have to do a mail merge, but the data is all in one column - 4 row
for each address - so I need to transpose it into 4 columns. Look
like this:

joe smith
123 main st
apt 4
yourtown, de 11111
suzy q
456 central st
suite 5
mytown, mn 45660
bob evans
...

I want it to look like this:

joe smith / 123 main st / apt 4 / yourtown, de 11111
suzy q / 456 central st / suite 5 / mytown, mn 45660
bob evans / ...

I've searched the forums, but haven't been able to find anything tha
tells me if I can do this without a macro -- I have no idea how t
create or record a macro..

 

[Frank Kabel]

Hi
if this data is on sheet1 in column A starting in cell A1 enter the
following formula on sheet 2 in cell A1
=OFFSET('sheet1'!$A$1,COLUMN()-1+(ROW()-1)*4,0)
and copy this 3 cells to the right and down as far as needed

 

[Ken Wright]

No real difference to Frank's, but assuming you were staying on the same sheet,
then in B1 enter the following and then copy across to Exx

=OFFSET($A$1,ROW()*4-(6-COLUMN(B1)),0)

When done, simply copy Cols B:E and paste special as values then delete Col A

 

[jlajoie]

Thank you both! I'm back in business..

 

[Ken Wright]

Good stuff

 

[navpoo]

Hi try this :

Say it looks like this

Name
Adress
Street
City
State
Zip

HIghlight these cells and copy them.

now place the cursor in the first cell from where you want paste, righ
click, choose paste special,place a chekmark on transpose and click o
ok.

Hope this works