Keeping cell references after sorting

[Mr_Patrick]

Hi everyone,

I have a lot of trouble with my cells references getting messed up after sorting.
I have two columns (about 3000 rows) which look like :
[TABLE="width: 500"]
<tbody>[TR]
[TD]2 and 3
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3 and 5
[/TD]
[TD]30
[/TD]
[/TR]
[TR]
[TD]6 and 7
[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD]8 and 9
[/TD]
[TD]80
[/TD]
[/TR]
[TR]
[TD]etc
[/TD]
[TD]etc
[/TD]
[/TR]
</tbody>[/TABLE]

Currently, the first columns are written like : cell A1 has input < 2 and 3> while cell A2 is :

[=LEFT(A1;FIND(" ";A1))+2&" and "&LEFT(A1;FIND(" ";A1))+3

and similarly for the other cells.
The problem is when I wish to sort both columns according to column B (from largest to smallest number), the referencing gets mixed up, I would want to get :
[TABLE="width: 500"]
<tbody>[TR]
[TD]8 and 9
[/TD]
[TD]80
[/TD]
[/TR]
[TR]
[TD]3 and 5
[/TD]
[TD]30
[/TD]
[/TR]
[TR]
[TD]6 and 7
[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD]2 and 3
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]etc
[/TD]
[TD]etc
[/TD]
[/TR]
</tbody>[/TABLE]

Anyone willing to help me out?
Thank you in advance.

 

[DILIPandey]

Okay.. but I am getting "4 and 5" in Cell A2 after using your formula.

Can you check?

Regards,
DILIPandey

 

[Mr_Patrick]

Okay.. but I am getting "4 and 5" in Cell A2 after using your formula.

Can you check?

Regards,
DILIPandey

Yes indeed. I am really sorry for the confusion! Cell in A2 should be <4 and 5>.

 

[DILIPandey]

okay.. because of formulas it is happening. you can do a pivot on the data and then do sorting, result will be:-

[TABLE="width: 153"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Row Labels[/TD]
[TD]Sum of b[/TD]
[/TR]
[TR]
[TD]8 and 9[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD]4 and 5[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]6 and 7[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]2 and 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]125[/TD]
[/TR]
</tbody>[/TABLE]

Which sources your data in column A and B without etc.


Regards,
DILIPandey

 

[Mr_Patrick]

okay.. because of formulas it is happening. you can do a pivot on the data and then do sorting

DILIPandey

Sorry for the late response.
I fail to see how I can do this. (This may be because I have never done a Pivot table). How do I get to write the data in the first column? ("2 and 3", "4 and 5",etc). I cannot really do this manually because this goes all the way until about 7000! Can a Pivot table generate this automatically? or do the references in the formula not get mixed up?

 

[Mr_Patrick]

OK, I solved my problem. I feel I bit silly for not finding this sooner. I simply copy my column and paste only the values. The formula is then gone and therefore no problem when sorting.

Thank you anyways.

 

[DILIPandey]

ha ha ... that is also a smart way if you don't want to retain formulas



Regards,
DILIPandey