How to Split one column in Excel into multiple columns of 55 rows

Question

I have a movie list with over 1500 entries (A1-A1500), all alphabetized (Thanks to Excel) and I would like to split that into multiple columns of 55 rows each on a separate sheet if possible. So A1-A55 from Sheet1 would go to A1-A55 on Sheet2, and A56-A110 would go to B1-B55 on Sheet2, so on and so on.

Each cell is an individual movie name and I would just like them to go from one long column to multiple columns with 55 rows in each.

Answer 1

If your data are on Sheet1, then fill in Sheet2!A1 with

=INDIRECT("Sheet1!R" & (COLUMN()-1)*55+MOD(ROW()-1,55)+1 & "C1", FALSE)

Drag/fill this down to row 55 and out to column AB.  The first parameter of INDIRECT is a constructed string of the form Sheet1!RnnnC1, with A1 evaluating to Sheet1!R1C1, A55 –> Sheet1!R55C1, B1 –> Sheet1!R56C1, etc…   AB15 evaluates to Sheet1!R1500C1. 

INDIRECT(reference, FALSE) retrieves the value from the cell referenced by reference using “R1C1” style, in which a row number and a column number are specified –– and of course these correspond to A1:A1500 on Sheet1.

This will cause your data to appear in columns of 55 rows on Sheet2; it will still be linked to Sheet1.  To make a static copy, “Copy” the data (A1:AB55 on Sheet2) and do “Paste Values”.  (To be safe, you might want to do this on Sheet3, then delete Sheet2.)

Answer 2

If your data is comma (,) delimited then select the rows first then goto Data -> Text to column -> Click on Delimited radio button and click next -> Now choose Delimiters (if it is comma delimited the click on comma check box) and click next and finally Finish button and see the result.