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.
Best Answer
If your data are on Sheet1, then fill in
Sheet2!A1
withDrag/fill this down to row 55 and out to column AB. The first parameter of
INDIRECT
is a constructed string of the formSheet1!RnnnC1
, withA1
evaluating toSheet1!R1C1
,A55
–>Sheet1!R55C1
,B1
–>Sheet1!R56C1
, etc…AB15
evaluates toSheet1!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 toA1: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.)