Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
    
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
3  
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
add comment

migrated from stackoverflow.com Dec 14 '12 at 17:04

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

2 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.