Sorting value with two decimal points in excel-CodePudding

I'm trying to sort an excel table by a certain column, these values are in the form 1.2.3 and I'm trying to find a way to sort them in ascending order. The built in excel sort function seems to do a good job however when numbers get into double digits it doesn't do exactly what I want.

Initial Value	Actual Outcome	Expected Outcome
1.1.1	1.1.1	1.1.1
1.1.2	1.1.11	1.1.2
1.11.1	1.1.2	1.1.11
1.2.1	1.11.1	1.2.1
1.1.11	1.2.1	1.11.1

The way I managed to do it is to split the column into three columns and sort them from right to left. However the issue with this is that it requires making new columns and then removing them which isn't something I can really ask the users of this sheet to do. There are also columns with data on both sides of this column in the sheet.

Is there a way to do the sorting in a single excel function or using VBA? Ideally it would be a single click to sort the table if that's possible.

CodePudding user response：

If you have Excel 365, you can use this formula to return a sorted output:

=SORTBY(sortRange,BYROW(sortRange,LAMBDA(arr,TEXTJOIN(".",TRUE,TEXT(TEXTSPLIT(arr,"."),"000")))))

TEXTSPLIT splits your dot separated string
TEXT then pads each segment with 0's.
- *If your values have more than three digits, change the padding
We then join things back together with TEXTJOIN
and SORTBY sorts the original array by the padded one we constructed.
the BYROW function allows us to enter this as a single formula which will have its results spill down, instead of doing a line by line activity.

If you have a larger table than just a single column, change the first argument of SORTBY to include the entire table, but leave SortRange only referring to the column you wish to base the sort on in the BYROW function.

CodePudding user response：

I would advise you to use a helper column, and in there you put the values, after having replaced the dots with a multiplication by one hundred.

I beg your pardon?

I mean that you create a UDF (a User-Defined Function) which performs the following conversion from your type of entry to an integer number:

1.2.3    => 1002003 ((1 * 100)   2) * 100   3
1.1.1    => 1001001 ((1 * 100)   1) * 100   1
1.11.1   => 1011001 ((1 * 100)  11) * 100   1
1.2      => 1002000 ((1 * 100)   2) * 100   0 (in case '1.2' equals '1.2.0')
...

That UDF can be created, using some basic string manipulation functions, combined with string-to-integer conversion functions (like CInt()).

Have fun

CodePudding user response：

Still working my way around Lambda, but this formula seems to work:

=BYROW(SORT(TEXTSPLIT(TEXTJOIN(",",FALSE,$A$2:$A$6),".",",")*1,{1,2,3}),LAMBDA(DataArray,TEXTJOIN(".",FALSE,DataArray)))

It doesn't use a helper column, but also doesn't sort the original data in place.

Thought I'd better add an explanation as I'm up against Ron. Need to up my game. :)

TEXTJOIN(",",FALSE,A2:A6) combines the whole list into a single list, separated by a comma.
TEXTSPLIT(<textjoin>,".",",")*1 splits it into a spilled range with each number in its own column. The *1 converts the text values to numeric values.
=SORT(<textsplit>,{1,2,3}) sorts the columns.
You could update the {1,2,3} array to SEQUENCE(,LEN(A2)-LEN(SUBSTITUTE(A2,".","") if you're going to add more columns - all columns must have same number of . though.
All the above makes up the DataArray used in the Lambda.
LAMBDA(DataArray,TEXTJOIN(".",FALSE,DataArray) joins the data back together.
BYROW sticks the data back together row by row.