I am using Ubuntu 22.04 and LibreOffice 24.2.0.3. Many times I have to sort data first by Category then by Year and then by Number. For that purpose I used to separate the Category Number and Year by using Text to Column Feature. Then I used to sort the data by Category then by Year and Then by Number. After sorting I used to again combine it by concatenate function. I want to know whether there is simpler way to do this.
You can try the prerelease version 24.8. It has a SORT function with multi-level sort. Ordinary installing will replace your normal version. Thus you might want to do an administrative installation. The download link is at the end of the regular download page.
Or another way: Define a Database range for your data. That is in Data > Define Range. Then select the range via Data > Select Range… and use the dialog from Data > Sort… The dialog has on its first page three fields for defining a multi-level sort, and on its second page it has an option to not change the original data but put the sort result into a new area.
1 Like
This is very easy to do with Base and Calc together. Pass Entire sheet to a formula, is this possible? - #16 by Villeroy
qa108711_source.ods (90.3 KB)
qa108711_dbConnection.odb (2.9 KB)
Call menu:Tools>Options>Base>Databases and register the odb file as “qa108711_dbConnection” (file name without .odb suffix).
Open the spreadsheet.
The first sheet has an unordered list.
The list on the second sheet has an ordered list linked to the one on the first sheet through a database query. Base can sort and filter much better than Calc.
Yes.
.
Create a formula, wich has a fixed format like “004_2001_000456.123” and can be used directly for your sort by
How to create the formula?
As first step, you have to extract the information to “replace” the text-in-colums either via some string-functions or you may use regular expressions.
.
Invented examples without testing, for a string in A1,wich contains colums separated by “;”:
=LEFT(A1, FIND(";"; A1)-1)
=SUBSTITUTE( REGEX(A1;";.x;"); ";";"")
.
The next step would be to fill shorter strings to achieve an identical width. Text should be filled to the right, numbers to the left.
.
Examples for string and Integer:
=LEFT("Category"&"____________________"; 20)
`=RIGHT(“000000”&1234; 6)
.
I suggest to create a formula for each needed part in a separate cell, before you concat the results as a third step.
.
Now sort by the created column. (It would also be possible to sort with the individual columns, then save. IMHO the last sort will be the default, if you sort again. But a combined column may be more reliable.) You may also use the coming SORTBY wich was announced for 24.8 in release-notes.
.
Last step is some cleanup of either hiding the intermediate columns or combine all formulas in on, and hope you never need to decode the result again…