1 2 3 4 5 6 7 8 9 10
(this is my initial data)
my output
1 2 3 4 5
6 7 8 9 10
is there anyway i can do this? i have around 400 rows of similar data
many thanks
(this is my initial data) my output
is there anyway i can do this? i have around 400 rows of similar data many thanks 


This question came from our site for professional and enthusiast programmers.
If this is a one time modification you can do as follow. If a repetitive task you definitely need a macro... Assuming your data are in line 1  x, column AJ and you want results in lines 1  2x, columns AE (the number of columns is not important as the method doesn't depends on that but it's easier to explain...) but I assume the 400 lines are a single "solid" table (no space or other objects) in the middle.
This is it, looks long saying this way but it is not to do. If you have a different number of columns (even if splitting is not in the middle) you can apply the same method splitting at the right column between the 2 sheets. 


1) Source data
2) Use Copy/Paste to get the following result
3) Add the following numbering
4) Sort data by B and C, this will produce:
This is applicable for any number of columnns. 

