1

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.

2

2 Answers 2

5

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

0
0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .