Tranposing maillist help please

[Salamone]

I want to transpose a maillist that is in one column into a standar
maillist

In other words, the way it is now is

NAME1
ADDRESS1
CITY1
STATE1
NAME2
ADDRESS2
etc

and the way I want it is

NAME1 ADDRESS1 CITY1 STATE1
NAME2 ADDRESS2 CITY2 STATE2

I know paste special transpose does this one at a time, is there an
way to do the whole list?

Thank

 

[Leo Heuser]

Salamone

Here's one way assuming your list starts in A2.

In C2 enter:

=OFFSET($A$2,(ROW()-ROW($C$2))*4+COLUMN()-COLUMN($C$2),0)

1. Copy C2 with the fill handle (the little square in the lower right
corner of
the cell) to F2.
2. While C2:F2 is selected copy down with the fill handle.
3. While the lot is selected make a copy <Ctrl>c and choose
Edit > Paste special and "Values". OK.

Now the list has been moved and all formulae removed.

ROW()-ROW($C$2) and COLUMN()-COLUMN($C$2) act as counters,
so if you start in another cell, e.g. H4, they have to be changed to
ROW()-ROW($H$4) and COLUMN()-COLUMN($H$4)

If each record has 3 fields replace *4 by *3 etc.

 

[Frank Kabel]

Hi
if your source data is on sheet 1 starting in A1 enter the following in
A1 on a separate sheet
=OFFSET('sheet1'!$A$1,(ROW()-1)*4+MOD(COLUMN()-1,4),0)
copy this 3 columns to the right and down

If you want to remove the formulas copy this output range and insert it
again as values (goto 'Edit - Paste Special' and choose 'Values' to do
this)

 

[Salamone]

Great reply Leo
Thanks
Frank Salamone:

 

[Salamone]

Thanks for your reply Frank
Helpful!
Frank Salamone:

 

[Leo Heuser]

You're welcome, Frank.
Appreciate the feedback.

LeoH