Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
If you just want to have them all on one page for a dense printout, you're better off putting them into a word document and formatting it for multi-column ("newspaper"-like) display. –  Adrian Pronk Feb 20 '13 at 8:41

2 Answers 2

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.)

share|improve this answer
    
Thank you, that worked perfectly. –  ignorantpower Feb 25 '13 at 1:18
    
Glad to help. May I draw your attention to the How do I ask questions? section of the Super User FAQ (Frequently Asked Questions) –– “When you have decided which answer is the most helpful to you, mark it as the accepted answer by clicking on the check box outline to the left of the answer.” If you do that, we both benefit. After you have earned some reputation, you can also vote up helpful answers by clicking the upward pointing arrow to the left of the answer. –  Scott Feb 25 '13 at 22:42

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.