[Solved] Sorting column cells with mixed data

Discuss the spreadsheet application

[Solved] Sorting column cells with mixed data

Postby D_Shane » Sun Sep 01, 2019 8:00 pm

I have data exported into xls with "time time cancelled prior to" and the data is in " 01 days 13 hours 10 min "

I need to sort longest to shortest but the data sorts 0, 1, 10, 100, then to 11,... 2, 20, etc.

I have replaced the words days, hours with colons decimals, and formatted the cells every possible way without changing the results.

Anyone have a way around this?
Attachments
cells.PNG
Last edited by robleyd on Mon Sep 02, 2019 1:26 am, edited 2 times in total.
Reason: Add green tick
OpenOffice 4.1.3 Windows 7
D_Shane
 
Posts: 2
Joined: Sun Sep 01, 2019 7:45 pm

Re: Sorting column cells with mixed data

Postby Villeroy » Sun Sep 01, 2019 9:11 pm

The whole column is text. Text sorts alphabetically.
In this case, alphabetical sorting would be OK if the contained numbers included leading zeroes as in "03 days 07 hours 08 min"
If you would install LibreOffice instead of OpenOffice (which is walking dead), you could switch on "natural sorting" which might do what you expect in this case.
https://libreoffice.org
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27731
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Sorting column cells with mixed data

Postby RusselB » Sun Sep 01, 2019 10:05 pm

To do this properly in OpenOffice Calc, I'd use helper columns to make the "time" into a proper hours:minutes, then sort using the helper column as the sort key.
An alternative would be to use Text to Columns, then sort using three sort keys.
If your current was in column A, then B would be the number of days, C -> the word "days", D -> number of hours, E -> the word "hours", F -> number of minutes, G -> the word "minutes"
Then sort everything using column B as your first sort key, D as your second, and F as your third.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5853
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Sorting column cells with mixed data

Postby Villeroy » Sun Sep 01, 2019 10:35 pm

I just tried with LibreOffice Calc and "natural sorting". The result is:
1 days 7 hours 33 minutes
2 days 5 hours 11 minutes
3 days 1 hours 2 minutes
3 days 13 hours 33 minutes
19 days 11 hours 3 minutes
29 days 3 hours 1 minutes
30 days 12 hours 22 minutes
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27731
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Sorting column cells with mixed data (Solved)

Postby D_Shane » Mon Sep 02, 2019 12:41 am

Perfect. Thanks
OpenOffice 4.1.3 Windows 7
D_Shane
 
Posts: 2
Joined: Sun Sep 01, 2019 7:45 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 9 guests