Excel Help

[Dnyan]

Hi,

I have a data of thousands of rows. and I want that data to be sort by numbers like.

<TABLE dir=ltr borderColor=#2de400 cellSpacing=1 cellPadding=2 width=330 border=1><TBODY><TR><TD width="27%" bgColor=#c0c0c0 height=4>

Actual Data​

​

​

​

​

</TD><TD width="31%" bgColor=#c0c0c0 height=4>

Wants to sort​

​

​

​

​

​

</TD><TD width="42%" bgColor=#c0c0c0 height=4>

Result After Sort​

​

​

​

​

</TD></TR><TR><TD width="27%" bgColor=#c0c0c0 height=4>

No​

​

​

​

​

</TD><TD width="31%" bgColor=#c0c0c0 height=4>

No.​

​

​

​

​

​

</TD><TD width="42%" bgColor=#c0c0c0 height=4>

No.​

​

​

​

​

</TD></TR><TR><TD width="27%" height=4>

2​

​

​

​

​

</TD><TD width="31%" height=4>

1​

​

​

​

​

​

</TD><TD width="42%" height=4>

1​

​

​

​

​

</TD></TR><TR><TD width="27%" height=4>

2​

​

​

​

​

</TD><TD width="31%" height=4>

1​

​

​

​

​

​

</TD><TD width="42%" height=4>

1​

​

​

​

​

</TD></TR><TR><TD width="27%" height=4>

3​

​

​

​

​

</TD><TD width="31%" height=4>

1​

​

​

​

​

​

</TD><TD width="42%" height=4>

1​

​

​

​

​

</TD></TR><TR><TD width="27%" height=4>

3​

​

​

​

​

</TD><TD width="31%" height=4>

1*​

​

​

​

​

​

</TD><TD width="42%" height=4>

2​

​

​

​

​

</TD></TR><TR><TD width="27%" height=4>

1*​

​

​

​

​

</TD><TD width="31%" height=4>

1*​

​

​

​

​

​

</TD><TD width="42%" height=4>

2​

​

​

​

​

</TD></TR><TR><TD width="27%" height=4>

1*​

​

​

​

​

</TD><TD width="31%" height=4>

1*​

​

​

​

​

​

</TD><TD width="42%" height=4>

2​

​

​

​

​

</TD></TR><TR><TD width="27%" height=4>

1​

​

​

​

​

</TD><TD width="31%" height=4>

2​

​

​

​

​

​

</TD><TD width="42%" height=4>

3​

​

​

​

​

</TD></TR><TR><TD width="27%" height=4>

2*​

​

​

​

​

</TD><TD width="31%" height=4>

2​

​

​

​

​

​

</TD><TD width="42%" height=4>

3​

​

​

​

​

</TD></TR><TR><TD width="27%" height=4>

3*​

​

​

​

​

</TD><TD width="31%" height=4>

2​

​

​

​

​

​

</TD><TD width="42%" height=4>

3​

​

​

​

​

</TD></TR><TR><TD width="27%" height=4>

2*​

​

​

​

​

</TD><TD width="31%" height=4>

2*​

​

​

​

​

​

</TD><TD width="42%" height=4>

1*​

​

​

​

​

</TD></TR><TR><TD width="27%" height=4>

3*​

​

​

​

​

</TD><TD width="31%" height=4>

2*​

​

​

​

​

​

</TD><TD width="42%" height=4>

1*​

​

​

​

​

</TD></TR><TR><TD width="27%" height=4>

1*​

​

​

​

​

</TD><TD width="31%" height=4>

2*​

​

​

​

​

​

</TD><TD width="42%" height=4>

1*​

​

​

​

​

</TD></TR><TR><TD width="27%" height=4>

1​

​

​

​

​

</TD><TD width="31%" height=4>

3​

​

​

​

​

​

</TD><TD width="42%" height=4>

2*​

​

​

​

​

</TD></TR><TR><TD width="27%" height=4>

2*​

​

​

​

​

</TD><TD width="31%" height=4>

3​

​

​

​

​

​

</TD><TD width="42%" height=4>

2*​

​

​

​

​

</TD></TR><TR><TD width="27%" height=4>

2​

​

​

​

​

</TD><TD width="31%" height=4>

3​

​

​

​

​

​

</TD><TD width="42%" height=4>

2*​

​

​

​

​

</TD></TR><TR><TD width="27%" height=4>

3*​

​

​

​

​

</TD><TD width="31%" height=4>

3*​

​

​

​

​

​

</TD><TD width="42%" height=4>

3*​

​

​

​

​

</TD></TR><TR><TD width="27%" height=4>

3​

​

​

​

​

</TD><TD width="31%" height=4>

3*​

​

​

​

​

​

</TD><TD width="42%" height=4>

3*​

​

​

​

​

</TD></TR><TR><TD width="27%" height=4>

1​

​

​

​

​

</TD><TD width="31%" height=4>

3*​

​

​

​

​

​

</TD><TD width="42%" height=4>

3*​

​

​

​

​

</TD></TR></TBODY></TABLE>

wants to sort data like this aslo if want to replace the * value it is replace both like if I replace 2* of all in sheets then it replace also 2.

Please suggest me the soluion of this.

Regards
Dnyan

 

[asking]

Here is one way ~

Assuming the data you wish to sort is in column A starting at cell A1

Select all column A, Click insert from the menu.
Now all your data you wish to sort is in column B
Select Cell A1 and enter or paste this formula ~
=IF(RIGHT(B1,1)="*",LEFT(B1)+0.1,B1)
and now fill down to the bottom of data in column B
Select all of column A again and click Copy, select Cell B1 and select paste special, values,
click OK.
Now select all column A and click delete from the menu.
Now you can sort as you wished.
You can now, from the menu click find and select, select replace, in the replace box type .1
in the with what box, type in what ever you want to replace the .1 with, click replace all.

Always save your sheet first, in case you stuff up.

 

[jberwald]

Highlight and right click choosing SORT and then CUSTOM SORT. Choose the column that contains your numbers on the left and on the far right under ORDER choose CUSTOM SORT from the drop down box. Under list entries type in: 1,2,3,1*,2*,3*. Hit the ADD button and close the box and you should be ready to go.

 

[SuperFerret]

if want to replace the * value it is replace both like if I replace 2* of all in sheets then it replace also 2.

Replace 2~* should work

~ should stop the * being a wildcard

 

[Dnyan]

Here is one way ~

Assuming the data you wish to sort is in column A starting at cell A1

Select all column A, Click insert from the menu.
Now all your data you wish to sort is in column B
Select Cell A1 and enter or paste this formula ~
=IF(RIGHT(B1,1)="*",LEFT(B1)+0.1,B1)
and now fill down to the bottom of data in column B
Select all of column A again and click Copy, select Cell B1 and select paste special, values,
click OK.
Now select all column A and click delete from the menu.
Now you can sort as you wished.
You can now, from the menu click find and select, select replace, in the replace box type .1
in the with what box, type in what ever you want to replace the .1 with, click replace all.

Always save your sheet first, in case you stuff up.

Hi,

Thank you very much its working

Thansk agian

Regards
Dnyan