[Solved] Sort does not work with vertical cell-references

[PaPyRene]

data and formulas sheet: sort gives incorrect cell-relations

Calc sheet data and formulas:

Code: Select all

!   !        A        !    B     !   C   !    D     !   E   !        F        !
+---+-----------------+----------+-------+----------+-------+-----------------+
! 1 !=C1&" "&B1       !FirstName !LstNme !Friend-Nm !=C1    !=E1&" "&D1       !  
+---+-----------------+----------+-------+----------+-------+-----------------+
! 2 !=C2&" "&B2       !Paul-Anne !Paulus !=B3       !=C3    !=E2&" "&D2       !
! 3 !=C3&" "&B3       !Anne-John !Anders !=B4       !=C4    !=E3&" "&D3       !
! 4 !=C4&" "&B4       !John-Paul !Johnes !=B2       !=C2    !=E4&" "&D4       !
+---+-----------------+----------+-------+----------+-------+-----------------+

Calc sheet data and formulas, unsorted result:

Code: Select all

!   !        A        !    B     !   C   !    D     !   E   !        F        !
+---+-----------------+----------+-------+----------+-------+-----------------+
! 1 !LstNme FirstName !FirstName !LstNme !Friend-Nm !LstNme !LstNme Friend-Nm !
+---+-----------------+----------+-------+----------+-------+-----------------+
! 2 !Paulus Paul-Anne !Paul-Anne !Paulus !Anne-John !Anders !Anders Anne-John !
! 3 !Anders Anne-John !Anne-John !Anders !John-Paul !Johnes !Johnes John-Paul !
! 4 !Johnes John-Paul !John-Paul !Johnes !Paul-Anne !Paulus !Paulus Paul-Anne !
+---+-----------------+----------+-------+----------+-------+-----------------+

Calc sheet data and formulas, sort results in incorrect cell-relationship,
(standing in cell C1, sorted on column C, containing column headings):

Code: Select all

!   !        A        !    B     !   C   !    D     !   E   !        F        !
+---+-----------------+----------+-------+----------+-------+-----------------+
! 1 !=C1&" "&B1       !FirstName !LstNme !Friend-Nm !=C1    !=E1&" "&D1       !
+---+-----------------+----------+-------+----------+-------+-----------------+
! 2 !=C2&" "&B2       !Anne-John !Anders !=B3       !=C3    !=E2&" "&D2       !
! 3 !=C3&" "&B3       !John-Paul !Johnes !=B1       !=C1    !=E3&" "&D3       !
! 4 !=C4&" "&B4       !Paul-Anne !Paulus !       =B5!    =C5!=E4&" "&D4       !
+---+-----------------+----------+-------+----------+-------+-----------------+

Calc sheet data and formulas, sorted incorrect result:

Code: Select all

!   !        A        !    B     !   C   !    D     !   E   !        F        !
+---+-----------------+----------+-------+----------+-------+-----------------+
! 1 !LstNme FirstName !FirstName !LstNme !Friend-Nm !LstNme !LstNme Friend-Nm !
+---+-----------------+----------+-------+----------+-------+-----------------+
! 2 !Anders Anne-John !Anne-John !Anders !John-Paul !Johnes !Johnes John-Paul !
! 3 !Johnes John-Paul !John-Paul !Johnes !FirstName !LstNme !LstNme FirstName !
! 4 !Paulus Paul-Anne !Paul-Anne !Paulus !         0!      0!0 0              !
+---+-----------------+----------+-------+----------+-------+-----------------+

cell D3 should contain B4 (not B1) and cell E3 should contain C4 (not C1)
cell D4 should contain B2 (not B5) and cell E4 should contain C2 (not C5)


How to get a correct sorted result without using fixed $ cells (then 'insert row' should give wrong results)?


Calc sheet data and formulas, with absolute references,
amazing but sort results also in incorrect cell-relationship,
(standing in cell C1, sorted on column C, containing column headings):

Code: Select all

!   !        A        !    B     !   C   !    D     !   E   !        F        !
+---+-----------------+----------+-------+----------+-------+-----------------+
! 1 !=C1&" "&B1       !FirstName !LstNme !Friend-Nm !=C1    !=E1&" "&D1       !  
+---+-----------------+----------+-------+----------+-------+-----------------+
! 2 !=C2&" "&B2       !Paul-Anne !Paulus !=B$3      !=C$3   !=E2&" "&D2       !
! 3 !=C3&" "&B3       !Anne-John !Anders !=B$4      !=C$4   !=E3&" "&D3       !
! 4 !=C4&" "&B4       !John-Paul !Johnes !=B$2      !=C$2   !=E4&" "&D4       !
+---+-----------------+----------+-------+----------+-------+-----------------+

Calc sheet data and formulas with absolutereferences, also sorted incorrect result:

Code: Select all

!   !        A        !    B     !   C   !    D     !   E   !        F        !
+---+-----------------+----------+-------+----------+-------+-----------------+
! 1 !LstNme FirstName !FirstName !LstNme !Friend-Nm !LstNme !LstNme Friend-Nm !
+---+-----------------+----------+-------+----------+-------+-----------------+
! 2 !Anders Anne-John !Anne-John !Anders !Paul-Anne !Paulus !Paulus Paul-Anne !
! 3 !Johnes John-Paul !John-Paul !Johnes !Anne-John !Anders !Anders Anne-John !
! 4 !Paulus Paul-Anne !Paul-Anne !Paulus !John-Paul !Johnes !Johnes John-Paul !
+---+-----------------+----------+-------+----------+-------+-----------------+

these absolute references are not that absolute

Code: Select all

!   !        A        !    B     !   C   !    D     !   E   !        F        !
+---+-----------------+----------+-------+----------+-------+-----------------+
! 1 !=C1&" "&B1       !FirstName !LstNme !Friend-Nm !=C1    !=E1&" "&D1       !  
+---+-----------------+----------+-------+----------+-------+-----------------+
! 2 !=C2&" "&B2       !Anne-John !Anders !=B$4      !=C$4   !=E2&" "&D2       !
! 3 !=C3&" "&B3       !John-Paul !Johnes !=B$2      !=C$2   !=E3&" "&D3       !
! 4 !=C4&" "&B4       !Paul-Anne !Paulus !=B$3      !=C$3   !=E4&" "&D4       !
+---+-----------------+----------+-------+----------+-------+-----------------+

cell D2 should contain B$3 (not B$4) and cell E2 should contain C$3 (not C$4)
cell D3 should contain B$4 (not B$2) and cell E3 should contain C$4 (not C$2)
cell D4 should contain B$2 (not B$3) and cell E4 should contain C$2 (not C$3)



CONCLUSION:
sort does not work in sheets containing vertical cell-references

(same problem in MS-Office Excel as in Open Office Calc)

[Villeroy]

It's a known bug in version 3.1

[gerard24]

It's a known bug in version 3.1

Not the same, i reproduce with LibreOffice 3.3.
Can you double check with OOo 3.3 ?

[Hagar Delest]

Same problem with 3.3 on Win XP.

[ken johnson]

Tried same in xl2003 and got same result as Calc 3.3.
I assume you just have to use absolute references.

Ken Johnson

[PaPyRene]

I now use:
OOo3.3.0 (2011) on Ms WinXP-SP3 and OOo1.0.1 (2002) on Linux Knoppix3.1

For both versions on a Calc data and formulas sheet: sort gives incorrect cell-relations...

Still not resolved after 9 years!

(using absolute references with $-sign is not an option because when inserting rows the result will also be incorrect)
(BTW: same incorrect result in MS-Excel97)

[Hagar Delest]

I remember that one: [Solved] Spreadsheet corruption caused by sorting. The issue (101690) has been fixed but in fact it is fixed when the cell references another cell on the same row.

Now, OOo keeps the relative position of the cell (reference being the sorted cell of the same row before sort). So it's not illogical but it doesn't suits your needs since the cells have not the same pattern.

You could report that but it may be a not that simple one to tackle: [Tutorial] Reporting bugs or suggestions.

[PaPyRene]

CONCLUSION:
sort does not work in sheets with vertical cell-references

(see my adapted original post)

[Villeroy]

I get the same result in a Gnumeric spreadsheet. Unlike Excel and Gnumeric, OOo comes with a relational database.

[MrProgrammer]

It appears that the OOo developers are aware of the situation.
Sort of range containing references to itself: adjust references to new cell containg value referenced prior to sort
Sorting of rows goes wrong when references are present
However they consider it as a potential product enhancement/feature, not a defect, so there's nothing to "fix".

Edit: Updated links above to use Apache site.

I believe I understand why the developers regard the sorting to be correct even though it's not doing what you want. And from reading other posts, most (all?) spreadsheet products will work the same way. However I can certainly see the value in providing an option to make Calc work the way you expect it to.

The difficulty is that when the rows are sorted it's the formulas in columns D and E that are rearranged, not the data. Let's look at row 2 (before the sort) for Paul-Anne Paulus. It contains =B3 in D2. This is not a reference to column 2 row 3! It is a relative reference to the cell two columns to the left and one row down from D2. During the sort this row gets placed at the bottom (line 4). So D4 will contain a relative reference to the cell two columns to the left and one down, that is =B5. Row 3 (Anne-John Anders before the sort) contains =B4 in D3 — two columns left, one row down. The sort places the row at the top (row 2), so D2 will contain =B3. Row 4 (John-Paul Johnes before the sort) contains =B2 in D4 — two columns left, two rows up. The sort places the row in the middle (row 3), so D3 will contain =B1. Your test shows that these are precisely the formulas in column D after the sort.

Sorting using absolute references doesn't work either. Let's look at row 2 (before the sort) for Paul-Anne Paulus. If it contains =$B$3 in D2, that's a reference to friend Anne-John Anders in row 3. During the sort this row gets placed at the bottom (line 4). So D4 will still have =$B$3, but the sort has placed that friend in row 2. The absolute references are to the friends' positions before the sort and the wrong friend appears in the cell.

So, what can you do? One quick workaround is to use absolute references but put the sorted data in a different location with Data, Sort, Options, More, Copy results to. A disadvantage is that the original pre-sort data must be preserved, though depending on why you're performing the sort that may be acceptable. For example, if it's just to print the data, the copy doesn't need to be retained after printing. Formulas can be easily changed from relative references to absolute referencces be selecting A2:F4 and pressing Shift+F4. After the sort you can change the references back to relative by pressing Shift+F4 thrice.

Another solution, which allows the data to be sorted in-place, is to permanently restructure it so that you're only sorting links to the names in a separate sheet. That is, on another "Names" sheet have

Code: Select all

FirstName 	LstNme 
Anne-John 	Anders 
John-Paul 	Johnes 
Paul-Anne 	Paulus 

and then in columns B through E put formulas

Code: Select all

=Names.$A$4	=Names.$B$4	=Names.$A$2	=Names.$B$2
=Names.$A$2	=Names.$B$2	=Names.$A$3	=Names.$B$3
=Names.$A$3	=Names.$B$3	=Names.$A$4	=Names.$B$4

In columns A and F you must use relative row references so that the references will change as the rows are reordered. This can be repeatedly sorted in place because we have eliminated the internal references to other rows of the table being sorted.

Perhaps a better way to do it is to introduce unique keys for the names on the other sheet:

Code: Select all

Key	FirstName 	LstNme 
AJA	Anne-John 	Anders 
JPJ	John-Paul 	Johnes 
PAP	Paul-Anne 	Paulus 

Edit: (Keys above must be in ascending order for LOOKUP function)

add names (Insert, Names, Define): Keys=$Names.$A$2:$A$4, First=$Names.$B$2:$B$4, and Last=$Names.$C$2:$C$4
and then in columns B through E put formulas

Code: Select all

=LOOKUP("PAP";Keys;First)	=LOOKUP("PAP";Keys;Last)	=LOOKUP("AJA";Keys;First)	=LOOKUP("AJA";Keys;Last)	
=LOOKUP("AJA";Keys;First)	=LOOKUP("AJA";Keys;Last)	=LOOKUP("JPJ";Keys;First)	=LOOKUP("JPJ";Keys;Last)	
=LOOKUP("JPJ";Keys;First)	=LOOKUP("JPJ";Keys;Last)	=LOOKUP("PAP";Keys;First)	=LOOKUP("PAP";Keys;Last)

As with the previous solution use relative references in columns A and E.

If you know how to use Base, there are no doubt simple ways to do this by storing the information in a database.

I realize none of these ideas are what you were hoping for, but given the present design of spreadsheets it seems impossible to sort data in-place when there are inter-row references. Optimistically, you understand why it works as it does.

[PaPyRene]

@MrProgrammer:

You made a very interesting analysis!

Your conclusion is the same as mine, but more clear:

it seems impossible to sort data in-place when there are inter-row references

Now the problem is that an ordinary user does not know that problem: one starts to sort an inter-row references table (from calc, excel, gnumeric, etc...) and uses the wrong results as a basis for a decision.
Something can go really wrong after that!

I (and with me other ordinary users) do not want to use a database for my simple calc-addressbook, where my problem starts using the inter-row reference cells 'father' and 'mother'; after sorting I saw that my uncle became my father and a friend became my brother because we got the same mother

I also thought at your suggestion to use an index for each row. And we are not the only one with that idea...

My point of view is that such a database based 'index-feature' should be automatically used in the background of calc each time someone uses 'sort'. In other words: calc developers could be the first to 'correct' the sort function in the spreadsheet world.

If that feature could be possible, then calc should be the only spreadsheet with a correct sort function!
Be aware that a well working sort, commented on internet, can boost up Open/Libre Office.

[Villeroy]

There is no need for a database based index feature in Calc since OOo comes with a database utility and Excel works very well with an app called "MS Query". Once you have stored your data in some kind of relational database and connected a "Base document" (actually a configuration file) to that database you can use the relational database row sets in Writer (reports, mail merge, business cards) , Calc (linked import ranges & pivot tables) or even Impress (copy to table grid).
Today's spreadsheets evolved from something that was never intended to replace mature databases.

[PaPyRene]

@Villeroy:

Didn't you read my post:

I (and with me other ordinary users) do not want to use a database for my simple calc-addressbook, where my problem starts using the inter-row reference cells 'father' and 'mother'; after sorting I saw that my uncle became my father and a friend became my brother because we got the same mother

[Villeroy]

You want to fix a screw with a hammer. Nobody wants to use databases nor spreadsheets. Everybody wants a new type of software which can not be compatible with existing applications. This type of software already exists but it is rarely known, rather expensive and hardly compatible to Excel or anything. You can import raw data. That's all.

[PaPyRene]

@Villeroy:
An ordinary user has never heard of a database.
He/she wants to make a quick list of data with a spreadsheet, using an easy feature to sort.

Today, computers are that fast enough to run very smart software, fe OOo calc with an intelligent sort-function.

I agree this will cost some serious efforts.
But this is not a threat, this is a challenge... resulting in an advantage...

[Villeroy]

Ordinary users use databases every day without noticing. Everything we do online, including this conversation, is stored in a database. Any business application, customer relationship, accounting, logistic is built on top of database technology. The trick is that the assumed end user does not see any database. Everybody can fill out a form, sort by date or subject, add data without bothering about file format or anything. Have you ever noticed what happens when an unexperienced user tries to sort a spreadsheet list? 90% of today's "spreadsheet solutions" are bullshit.

[PaPyRene]

@Villeroy:

Of course, as you wrote

Ordinary users use databases every day without noticing.

But, as a I wrote earlier , my point is:

An ordinary user has never heard of a database.
He/she wants to make a quick list of data with a spreadsheet, using an easy feature to sort.

I mean: he/she will never start OOo Base to make an address-list; not even will our 50 experienced users in our companies where we use OOo, every day... Base is a database system in their mind, not a spreadsheet.

[mriosv]

Use a formula to relation rows in a table that works as database, seem not to be a good solution, even with the performance to do the selection.
The field to do a join between rows, better with a text or a number. With data validity from a cell range, it can be done, can select from column data and no problem with sorting.
Change the algorithm to do the sorting, could be not so easy and also affect the sorting performance. To manage in a different way the relations to cells inside the table than the other cells.

[Villeroy]

I'll attach a very quick draft copied from your spreadsheet with some additional data and a usable form with a list box of friends referencing persons in the same table. I used this type of list boxes with 10 thousands of persons.
A person may not have any friend but the friend must not be the same person. Duplicate combinations of first name and last name are rejected as well (case-insensitive). It refuses to store the record if rules are violated.
Each record is stored automatically when you move to another table row. No file issues, no format issues, no duplicates.

It is possible to connect the Base document (*odb) to a MySQL server so every users works with the same pool of data.
It is also possible to store copies of such input forms as stand-alone documents (Writer, Calc, Draw) on the client machines so the users never see any Base document which serves as a mere configuration file.