Formula Advice on Sorting Decimal Values from lowest value to Highest value

[VBA learner ITG]

Hi Peers,

I appreciate this may have been asked before but i am having issues sorting values with decimal points from low to high.

In Column A I have decimal values and in column B i have applied this formula to help with the sortation: =INT(A2)&"."&TEXT(RIGHT(A2,LEN(A2)-(FIND(".",A2))),"000")

The issue it is having is when the value is for example 1.10 it shows thew value as 1.001 when it should be 1.010

Any advice appreciated on how to resolve my issue?

CUSTOMER SEQUENCE	SORT SEQUENCE WITH FORMULA
1.1	1.001
1.2	1.002
1.3	1.003
1.4	1.004
1.5	1.005
1.6	1.006
1.7	1.007
1.8	1.008
1.9	1.009
1.1	1.001
1.11	1.011
1.12	1.012
1.13	1.013
1.14	1.014
1.15	1.015
1.16	1.016
1.17	1.017
1.18	1.018
1.19	1.019
1.2	1.002

 

[jasonb75]

So are you saying that the first 1.1 a the top of the list should be 1.001, but the second 1.1 that you've highlighted should be 1.010?

Unless there is something else in data to help identify which is which, what you are asking will not be possible. You can not have identical values with different values if you want to sort them. Anything that does appear to work would be too error prone to be useful.

This formula is an answer based on the limited information provided, but it is definitely not a solution.'

=INT(A2)+TEXT(COUNTIF(A$2:A2,">="&INT(A2)),"""0.""000")

 

[etaf]

not 100% sure what you are trying to achieve
1.1 = 1.01
but
1.2 = 1.002
1.3 = 1.003
AND
1.18 = 1.018

So depending on how many decimal places you have depends on if adding 1 or 2 zeros

1.21 = 1.021
1.33 = 1.033
BUT
1.20 = 1.002
1.30 = 1.003

 

[mikerickson]

as numbers, 1.10 = 1.1
as strings "1.10" <> "1.1"
The problem isn't with the formlua, but that column A are numbers not strings.

 

[VBA learner ITG]

So are you saying that the first 1.1 a the top of the list should be 1.001, but the second 1.1 that you've highlighted should be 1.010?

Unless there is something else in data to help identify which is which, what you are asking will not be possible. You can not have identical values with different values if you want to sort them. Anything that does appear to work would be too error prone to be useful.

Yes you are correct when i enter 1.1 it shows as 1.1 and when i enter on the row i mentioned 1.10 it shows as 1.1 even converting to decimal places doesnt work.

 

[VBA learner ITG]

not 100% sure what you are trying to achieve
1.1 = 1.01
but
1.2 = 1.002
1.3 = 1.003
AND
1.18 = 1.018

So depending on how many decimal places you have depends on if adding 1 or 2 zeros

1.21 = 1.021
1.33 = 1.033
BUT
1.20 = 1.002
1.30 = 1.003

Yes you are correct when i enter 1.1 it shows as 1.1 and when i enter on the row i mentioned 1.10 it shows as 1.1 even converting to decimal places doesnt work and even when i apply the helper formula

 

[mikerickson]

1.10 = 1.1
They are exactly the same number.

 

[etaf]

do you want this
=INT(A2)+MOD(A2,1)/10

Book2
	A	B	C
1	CUSTOMER SEQUENCE	SORT SEQUENCE WITH FORMULA
2	1.1	1.001	1.01
3	1.2	1.002	1.02
4	1.3	1.003	1.03
5	1.4	1.004	1.04
6	1.5	1.005	1.05
7	1.6	1.006	1.06
8	1.7	1.007	1.07
9	1.8	1.008	1.08
10	1.9	1.009	1.09
11	1.1	1.001	1.01
12	1.11	1.011	1.011
13	1.12	1.012	1.012
14	1.13	1.013	1.013
15	1.14	1.014	1.014
16	1.15	1.015	1.015
17	1.16	1.016	1.016
18	1.17	1.017	1.017
19	1.18	1.018	1.018
20	1.19	1.019	1.019
21	1.2	1.002	1.02
Sheet1

Cell Formulas
Range	Formula
C2:C21	C2	=INT(A2)+MOD(A2,1)/10

 

[VBA learner ITG]

The only way to achieve what i want is to convert the column to a text string instead of the current numerical

do you want this
=INT(A2)+MOD(A2,1)/10

Book2
	A	B	C
1	CUSTOMER SEQUENCE	SORT SEQUENCE WITH FORMULA
2	1.1	1.001	1.01
3	1.2	1.002	1.02
4	1.3	1.003	1.03
5	1.4	1.004	1.04
6	1.5	1.005	1.05
7	1.6	1.006	1.06
8	1.7	1.007	1.07
9	1.8	1.008	1.08
10	1.9	1.009	1.09
11	1.1	1.001	1.01
12	1.11	1.011	1.011
13	1.12	1.012	1.012
14	1.13	1.013	1.013
15	1.14	1.014	1.014
16	1.15	1.015	1.015
17	1.16	1.016	1.016
18	1.17	1.017	1.017
19	1.18	1.018	1.018
20	1.19	1.019	1.019
21	1.2	1.002	1.02
Sheet1

Cell Formulas
Range	Formula
C2:C21	C2	=INT(A2)+MOD(A2,1)/10

In row 11 of your example the value should be 1.10 and not 1.01 and row 21 should be 1.20 and not 1.02

I have converted the values to be stored as text and it works.

CUSTOMER SEQUENCE	SORT SEQUENCE
'1.1	1.01
'1.2	1.02
1.3	1.03
'1.4	1.04
'1.5	1.05
'1.6	1.06
'1.7	1.07
'1.8	1.08
'1.9	1.09
'1.10	1.10
'1.11	1.11
'1.12	1.12
'1.13	1.13
'1.14	1.14
'1.15	1.15
'1.16	1.16
'1.17	1.17
'1.18	1.18
'1.19	1.19
'1.20	1.20

 

[VBA learner ITG]

1.10 = 1.1
They are exactly the same number.

Yes you are correct but when i am trying to sort the values of 1.1 and 1.10 the sortation becomes incoprrect because 1.10 shouldnt be groued with 1.1 in the first row of my data.