Auto sort routine
EE Professionals,
I have a macro that looks for changes in a column of numbers. When a number is added to the last column, it automatically "fits" the number into the proper sequence (Low to High).
Example;
1
2
3
4
5
Now if I add 3.2 in the last cell, it will reorder the column as;
1
2
3
3.2
4
5
The problem is, if I add 3.2.1, it will put it at the end of the column instead of after 3.2; apparently not recognizing the expanded requirement for another sub.
Any ideas on how to fix the recognition of the number or position based on this schema?
Thank you,
B.
I have a macro that looks for changes in a column of numbers. When a number is added to the last column, it automatically "fits" the number into the proper sequence (Low to High).
Example;
1
2
3
4
5
Now if I add 3.2 in the last cell, it will reorder the column as;
1
2
3
3.2
4
5
The problem is, if I add 3.2.1, it will put it at the end of the column instead of after 3.2; apparently not recognizing the expanded requirement for another sub.
Any ideas on how to fix the recognition of the number or position based on this schema?
Thank you,
B.
I think to get you the desired result, you may need more. Because your sorted list should be ordered like:
1
2
3
3.2
4
5
.....
9
9.2
10.0
11.2
...
Am I right?
How many decimal points (maximum) you expect to see in your data to be sorted?
Are you okay if the solution uses an additional auxilary column for sorting?
1
2
3
3.2
4
5
.....
9
9.2
10.0
11.2
...
Am I right?
How many decimal points (maximum) you expect to see in your data to be sorted?
Are you okay if the solution uses an additional auxilary column for sorting?
Which version of Excel?
ASKER
I'm using Excel 2010. I'd like to have two decimal points such as in an outline. I tried to define the cells as "text" but it did not sort correctly with 2 decimal points.
B.
B.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
matthewspatrick! This is great! Thank you....... great job.
B.
B.
ASKER
I've requested that this question be closed as follows:
Accepted answer: 0 points for Bright01's comment http:/Q_27346423.html#36720769
for the following reason:
Great solution! You are clearly a "problem solver" or "puzzle master"!<br /><br />B.
Accepted answer: 0 points for Bright01's comment http:/Q_27346423.html#36720769
for the following reason:
Great solution! You are clearly a "problem solver" or "puzzle master"!<br /><br />B.
I think you selected your own comment as the answer in error.
ASKER
Excellent solution!
Much thanks!
B.
Much thanks!
B.
3.2.1 is text
numbers are < text
Change your values from:
1
2
3
3.2
3.2.1
4
5
to
'1
'2
'3
'3.2
'3.2.1
'4
'5
Now auto-sort will work as expected.
It is not enough to change the cell-type to "text", you have to use the single quotes.
There is probably a programmatic way of handling this, but the above is an explanation of what you are seeing.