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

  • Thread starter Thread starter jlajoie
  • Start date Start date
J

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..
 
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
 
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
 
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
 
Back
Top