# I need to arrange my data cells in excel in a way that rows are rearranged as shown

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

-

## migrated from stackoverflow.comDec 14 '12 at 17:04

This question came from our site for professional and enthusiast programmers.

There is always a way, but maybe you just have to try harder berfore asking. – twn08 Dec 14 '12 at 8:29
i tried using formulas..since i am not very well with macros.not able to code it..:) – user1903396 Dec 14 '12 at 8:33
It is not clear from your question whether "1 2 3 4 5 6 7 8 9 10" is one string in a cell or 10 values. It is also unclear whether this is one time tasks (which can be easily done manually) or this has to be done frequently. You can also describe attempts you've made before asking. – twn08 Dec 14 '12 at 8:36
Its ten values in ten cells in a row.i have to shift the last five to a row below the first five. so instead of ten columns and 1 row..i will have 5 columns and 2 rows. – user1903396 Dec 14 '12 at 8:46
i tried a formula – user1903396 Dec 14 '12 at 8:49

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.

• insert a column before A so data are in B-K now;
• fill column A with sequencial numbers (1-400) (use excel autofill with dragging);
• copy the whole sheet to a 2nd temp sheet;
• in original sheet: remove columns G-K
• in original: insert column between A and B and fill it with 1 on all lines;
• in copy: remove columns B-F
• in copy : insert column between A and B and fill it with 2 on all lines;
• copy all cells on temporary 2nd sheet and paste in original sheet below the other lines;
• sort using A as primary and B as secondary columns;
• remove cloumns A and B;

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

``````    A     B
1  99   100
2  98   101
3  97   102
4  96   103
``````

2) Use Copy/Paste to get the following result

``````    A
1  99
2  98
3  97
4  96
5 100
6 101
7 102
8 103
``````

3) Add the following numbering

``````    A  B   C
1  99  1   1
2  98  2   1
3  97  3   1
4  96  4   1
5 100  1   2
6 101  2   2
7 102  3   2
8 103  4   2
``````

4) Sort data by B and C, this will produce:

``````    A  B   C
1  99  1   1
2 100  1   2
3  98  2   1
4 101  2   2
5  97  3   1
6 102  3   2
7  96  4   1
8 103  4   2
``````

This is applicable for any number of columnns.

-
This is an interesting technique, but as described is incomplete - could you show how sorting "by B and C" generates the outcome the original post requested? Do you mean "Sort by B then C"? Also: when applied to more than 2 columns, do you need to add more columns -- D, E, F to match the sample data the OP provided? – Charles Lindsay Dec 14 '12 at 17:18
Step 3 is not very easy to apply on large datasets. Column B is OK as you only need to fill the first sequence and use Excel fill option to fill the rest but Column C will need to be almost manually filled for each initial number and copy/pasted or with a formula and transformed in text before sorting. If need to automate, tt depends on the number of rows to fill step 3. – laurent Dec 14 '12 at 17:57
@CharlesLindsay I've added result of sorting. I think this is exactly what was requested. Also it can be easily applied when there are more columns - e.g. data are columns A:F, you can just copy data from D:F below data in A:C and go to step 3.If this is manual non-repetitive task, this can solve it within one minute even for the biggest excel sheet. – Tomas Greif Dec 14 '12 at 19:57