Selected Answer
Now here is a function that you can adjust and then work with. It will split a range of two columns into several columns of 2 columns each, for example, 4 x 2 columns (or 2 x 2 columns - not clear what you want but this formula can do either). The idea is that you paste the formula into the first cell of the target range, adjust it, and then copy it to all the other cells of the target range.
=INDEX($A$2:$B$15,INT((ROW()-ROW($I$2))/12)*12+ROW()-ROW($I$2)+(INT((COLUMN()-COLUMN($I$2))/2)*3)+1,IF(MOD(COLUMN($I$2),2), MOD(COLUMN()-COLUMN($I$2),2)+1, 2-(MOD(COLUMN()-COLUMN($I$2),2))))
And this is how you adjust it:-
- $A$2:$B$15 is the range of your original data. Call it the Input range. It could be A1:A2000. It could also be a named range which is defined to be dynamic.
- $I$2 is the anchor cell (top left corner) of the target range. Change all instances of it to the cell that you use. In my test I created the formula in I2 and then copied it to I2:P4.
- 12 is the product of a multiplication of number of rows * number of data columns in the target range. Each data column can consist of several sheet columns. In your example there are 2 sheet columns to each data column. I tested with with 4 data columns of 3 rows each (4 * 3 = 12). In your example you would have 50 or 500 rows and either 2 or 4 data columns, each with 2 sheet columns. Change all instances of 12 to the number derived from your requirement.
- Several instances of 2 represent the number of sheet columns per data column. For your present purposes this needs no change. But if you ever handle more than 2 sheet columns per data column make sure you don't confuse the solitary 2's with those which are part of a cell address, like $I$2. Only the former are related to the number of sheet columns in the output range.
- Finally, there is a 3 in the formula which controlls the number of rows in the target (output) range. You would want 50 or 5000 rows (not clear).
Now, if you have 500 rows in your output range the first 500 cells from your input range would appear in the first column. If you only want the first 50 on the first page and continue with row 51 on the second you might create each page individually, assigning different input ranges. The formula could be expanded to sort the data differently but the effort wouldn't be worth the result because you would lose the flexibility to adjust your page size.
As for changing the input data I recommend not to conflate input with output. So long as you keep the input data as your source data and changes you make to them would be instantly reflected in the output. It's only wha you want to treat the output data as original data when you start to have a problem that needs more work to solve. Your 4-columned output is a "report" which is created from the "data" in the original columns. Best practise suggests that you always keep your data. You can create many kinds of reports from them. Once you have them in 4-column format your options have been drastically reduced.
