OOo 3.1 - Sort in reverse order

Discuss the spreadsheet application
Post Reply
andyqkw
Posts: 25
Joined: Thu Nov 22, 2007 4:24 pm
Location: Singapore

OOo 3.1 - Sort in reverse order

Post by andyqkw »

Just installed OOo 3.1 and the sort function in Calc are not functioning correctly.

Clicking on the sort icon A -> Z returns the result as Z -> A instead.

Same goes to Z -> A, returning the result as A -> Z.
OOo 3.1.1 on Windows Vista Home Premium SP2 64-bit
Nikos
Posts: 172
Joined: Mon Dec 17, 2007 11:50 am

Re: OOo 3.1 - Sort in reverse order

Post by Nikos »

Sounds strange...

but cannot replicate it.
LibreOffice 4.0.5 on OpenSuse 12.3/64bit/KDE4.10 (Version from the TDF page) + LibreOffice 4.1.1 on Debian Wheezy/64bit/KDE4.8 + LibreOffice 4.1.1 on Win7/64bit
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: OOo 3.1 - Sort in reverse order

Post by FJCC »

I also can't replicate the effect. Could you post some details about what you are trying to sort and what happens?
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
TheGurkha
Volunteer
Posts: 6482
Joined: Thu Mar 13, 2008 12:13 pm
Location: North Wales, UK.

Re: OOo 3.1 - Sort in reverse order

Post by TheGurkha »

Strange, indeed. Look at my two screen shots. They both have the same dummy data. One of them is sorted A->Z and the other Z->A. In the Z->A order it doesn't seem to be sorting the very first cell.
Sorted A -> Z
Sorted A -> Z
Sorted Z -> A
Sorted Z -> A
Ubuntu 14.10 Utopic Unicorn, LibreOffice Version: 4.3.3.2
Gurkha Welfare Trust
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: OOo 3.1 - Sort in reverse order

Post by acknak »

I suspect that Calc finds a text value in the first cell and assumes it's a column heading, so it stays at the top.
AOO4/LO5 • Linux • Fedora 23
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: OOo 3.1 - Sort in reverse order

Post by Villeroy »

The first row is treated as column label. It's a shame that the developers are unable to write a working label detector.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
andyqkw
Posts: 25
Joined: Thu Nov 22, 2007 4:24 pm
Location: Singapore

Re: OOo 3.1 - Sort in reverse order

Post by andyqkw »

Did some further testing myself...

This document of mine was initially created with OOo 2.X and is saved in .xls format.

On one spreadsheet (created this year), Calc sorts the data in reverse order for row 2 - 46 (row one being the header) and anything from row 47 onwards sorts in the correct order.

On another spreadsheet (created few years back), Calc sorts in complete random order regardless of rows number.

Tried creating a new spreadsheed with OOo 3.1 but couldn't reproduce the error???
OOo 3.1.1 on Windows Vista Home Premium SP2 64-bit
andyqkw
Posts: 25
Joined: Thu Nov 22, 2007 4:24 pm
Location: Singapore

Re: OOo 3.1 - Sort in reverse order

Post by andyqkw »

I've uploaded a sample file for all experts out there to see, it's sorting the column in random order...
Attachments
Sample.xls
(9 KiB) Downloaded 192 times
OOo 3.1.1 on Windows Vista Home Premium SP2 64-bit
Dave
Posts: 1011
Joined: Sun Dec 23, 2007 6:53 pm

Re: OOo 3.1 - Sort in reverse order

Post by Dave »

In the sort window, look for the "Options" tab, and "Range contains column labels". Deselect that to include the top item.

David.
andyqkw
Posts: 25
Joined: Thu Nov 22, 2007 4:24 pm
Location: Singapore

Re: OOo 3.1 - Sort in random order

Post by andyqkw »

Tried that, doesn't work.
OOo 3.1.1 on Windows Vista Home Premium SP2 64-bit
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: OOo 3.1 - Sort in reverse order

Post by Villeroy »

Running Sun's version of OOo3.1, I changed the description values in the above "Sample.xls" to ...

Code: Select all

one
two
three
four
five
six
... so I have better control which text is sorted with which number.

I click any cell in the list's first column and quick-sort [A-Z]:

Code: Select all

Description	Amount
five	$5.00
four	$4.00
one	$1.00
six	$6.00
three	$3.00
two	$2.00
It correctly sorts the first column alphabetically, detecting the first header row.
Hitting the [Z-A] button, I get the exact reverse order.

Code: Select all

Description	Amount
two	$2.00
three	$3.00
six	$6.00
one	$1.00
four	$4.00
five	$5.00
Focussing a cell in the second column I get from [A-Z]

Code: Select all

Description	Amount
one	$1.00
two	$2.00
three	$3.00
four	$4.00
five	$5.00
six	$6.00
and [Z-A]

Code: Select all

Description	Amount
six	$6.00
five	$5.00
four	$4.00
three	$3.00
two	$2.00
one	$1.00
In either case the sorted range gets selected if I focussed just a single cell.
menu:Data>Sort... gives the same 4 results, sorting each of the two columns in ascending and descending order.

I append values "seven" and 7 in row #8, click [A-Z], [A-Z] and to my surprise the new row gets included although I did not insert a new row of cells into the existing data range. Everything sorts perfectly.
Alphabetical by column A ("Description"):

Code: Select all

Description	Amount
five	$5.00
four	$4.00
one	$1.00
seven	$7.00
six	$6.00
three	$3.00
two	$2.00
the reverse order is exactly revers, numeric sorting of column B ("Amount") works as well.

Now I define a database range in menu:Data>Define... with "List" as name, $Sample.$A$1:$B$19 as reference and first row as column headers. It includes the whole assumed list including the yet empty cells.
I repeat all the above tests, append "eight" and 8, do it again and everything sorts just perfectly, but now selecting the whole defined data range A1:B19.
I clear some values in both columns and everything sorts as expected.
Completely empty rows are excluded from sorting. They always stick at the bottom of the list whereas empty values with associated neigbour values are sorted (empty is considered larger than any number or text, all text is larger than any number).

The developers did a very good job when they newly implemented the spreadsheet sorting after so many years of valid complaints. One embarrassing error issue 101690 slipped in, though. But that bug has nothing to do with the constant values in this example.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: OOo 3.1 - Sort in reverse order

Post by Villeroy »

P.S. and off-topic:
Demo of the stable sorting, introduced in v3.1:

Randomly sorted integers:

Code: Select all

1	2	4	10
0	1	3	8
1	3	7	15
0	1	3	7
0	0	0	2
0	0	1	3
1	2	5	12
0	0	0	1
1	3	6	13
1	2	4	9
1	3	6	14
1	3	7	16
0	0	1	4
0	1	2	6
0	1	2	5
1	2	5	11
Activate a cell in column #1 and sort, then sort column #2, #3 and #4.
The result is a sort order by more than three columns:

Code: Select all

0	0	0	1
0	0	0	2
0	0	1	3
0	0	1	4
0	1	2	5
0	1	2	6
0	1	3	7
0	1	3	8
1	2	4	9
1	2	4	10
1	2	5	11
1	2	5	12
1	3	6	13
1	3	6	14
1	3	7	15
1	3	7	16
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
andyqkw
Posts: 25
Joined: Thu Nov 22, 2007 4:24 pm
Location: Singapore

Re: OOo 3.1 - Sort in random order

Post by andyqkw »

Numbers sorts fine for single column sorting. But for text, the first row/column is ignored.

This issue was mentioned in this thread http://user.services.openoffice.org/en/ ... =9&t=19681 and others as well...

It's a shame that OOo was not able to release a patch file for this problem instead of having users waiting till Aug 09 or even later for 3.1.1 to be released.
OOo 3.1.1 on Windows Vista Home Premium SP2 64-bit
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: OOo 3.1 - Sort in reverse order

Post by Villeroy »

If your list is not supposed to have column labels, define a database range and unckeck "Contains column labels" and use the menu command. I'd simply add column labels. They don't hurt.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: OOo 3.1 - Sort in reverse order

Post by acknak »

I must be missing something: I don't understand why we're running around in circles here.

I can't reproduce any problem using the "Sample.xls" file that was attached here. Does anyone see a problem with that file? If so, what exactly?
andyqkw wrote:This issue was mentioned in this thread...
Sorry, but as I understand the situation, that is an entirely separate problem with sorting in OOo 3.1. That problem only affects sorting cells that contain formulas. Your sample file contains only static (unchanging) data (excepting the total cell, which I assume is not part of area you want to sort).

Can someone (andyqkw?) please provide a sample file, and/or instructions to reproduce the problem? A description or screenshots like Gurkha's might be helpful also. If someone's already done that, and I just missed it, then I apologize up front ;-)
AOO4/LO5 • Linux • Fedora 23
Post Reply