split one column into multiple columns

  • Thread starter Thread starter SandyDi
  • Start date Start date
S

SandyDi

I want to take one long column (544 rows) and split it into columns of 50.
Is there a way to do that in Excel?
 
Source data (ie your long col) assumed running in A2 down ...
In say, B2: =OFFSET($A$2,COLUMNS($A:A)*50-51+ROWS($1:1),)
Copy down by 50 rows to B51, fill across as far as required to exhaust.
Continuous zeros will signal completion of data extract from col A. Any good?
hit the YES below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
 
Say your datalist is in A1 to A544.

Enter this formula *anywhere*, and copy across 10 columns (that's 11 columns
*total*),
THEN, copy that 11 column selection down 50 rows:

=INDEX($A$1:$A$544,ROWS($1:1)+50*(COLUMNS($A:A)-1))

If you're looking to eventually delete the *original* datalist, you must
first remove the formulas from the *new* datalist, leaving just the values
behind.

With the new datalist *still* selected from the copy,
right click in the selection and choose "Copy",
right click again and choose "Paste Special",
click on "Values",
then <OK>, then <Esc>, and you're done.

You now have only data in the new locations, so the original list can now be
removed.
 
Back
Top