1 2 3 4 5 6 7 8 9 10
(this is my initial data)
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
migrated from stackoverflow.com Dec 14 '12 at 17:04
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 A-J and you want results in lines 1 - 2x, columns A-E (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.