Coverting information in rows to colums

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Information is in one column many rows.
a1 1234
a2 henry
a3 jones
a4 43 melrose st
a5 wallyvile
need to transpose this information into seperate colums
example column a 1234 column b henry column c jones etc
this spreadsheet has over 300 address
 
Always 5 rows per address and no blank cells between addresses?

A1:A5, A6:A10, A11:A15, ...

If yes, put this in B1:
=INDEX($A:$A,(ROW()-1)*5+COLUMN()-1)
Drag to F1.

Select B1:F1 and drag down until you see the formulas start returning 0's
(you've run out of data). (Then clean up those 0's.)

If you want,
Select B:F
edit|copy
edit|paste special|Values

Delete column A.

If you have varying amount of rows per address or gaps between addresses, then
this won't work.
 
Thank you this worked

Dave Peterson said:
Always 5 rows per address and no blank cells between addresses?

A1:A5, A6:A10, A11:A15, ...

If yes, put this in B1:
=INDEX($A:$A,(ROW()-1)*5+COLUMN()-1)
Drag to F1.

Select B1:F1 and drag down until you see the formulas start returning 0's
(you've run out of data). (Then clean up those 0's.)

If you want,
Select B:F
edit|copy
edit|paste special|Values

Delete column A.

If you have varying amount of rows per address or gaps between addresses, then
this won't work.
Click to expand...
 
Back
Top