Created attachment 121289 [details] Test file demonstrating sorting problem When searching with vlookup date, then copying data without formulas to te new sheet, sorting does not work correctly. Check the attached ODT test file with 3 sheets: "List" containing formulas, which are searching data from the "Inventory" sheet and "Summary" sheet is a result of "copy/paste special" of "List" with only Text, Numbers, Date&Time and Formats included. Now sorting "Summary" sheet by "Contact" seems to be incorrect: - Cells with empty value have changed to show 30.12.1899 - non-existing "host" cells showing correctly #N/A - Cells with existing Date value showing correctly When trying to sort now by "Contact", only the cells with existing Date value seems to be sorted correctly. #N/A and originally empty but now with bogus date value filled cells doesn't seem to be sorting at all.
Running LibreOffice 5.0.3.2 on Linux and LibreOffice 5.0.4.1 on 64-bit Windows 7.
Now tested also under LibreOffice 4.2.8 running on Linux RHEL 6.7 64-bit, same behavior there. Difference between Linux and Windows seems to be that at least on 5.0.4.1 on Windows 7 64-bit those empty Date cells on the first sheet collected with VLOOKUP show up correctly as empty but under Linux versions 4.2.8 and 5.0.3.2 those already show date 30.12.1899 when cell is configured as Date or 0, if cell format is text/general. After copy / paste special all versions have changed those empty cells incorrectly to 0 / 30.12.1899.
Hi, With Win maybe you have disable the option for show zero values in Menu/Tools/LibreOffice calc/View From a VLOOKUP it's not possible to get an empty cell at most you can get a cell with an empty string. List.B2 =IF(VLOOKUP(A2;Inventory.$A$1:$B$7;2;0);VLOOKUP(A2;Inventory.$A$1:$B$7;2;0);"") Dates in calc are stored as integers being the zero 31/12/1899. https://help.libreoffice.org/Calc/Date_and_Time_Functions
You were correct about that "Show zero values" but the original problem with the sorting is still there, i.e. #N/A and zero values are treated equally, which should not be the case.
Hi Ari, You have to first examine the way LO treats empty cells. https://help.libreoffice.org/Calc/Handling_of_Empty_Cells Regards
(In reply to Ari Latvala from comment #4) > You were correct about that "Show zero values" but the original problem with > the sorting is still there, i.e. #N/A and zero values are treated equally, > which should not be the case. How then?
I would prefer similar behavior than on Excel 2013. There that date sorting on "Summary" will arrange empty dates as oldest (0.1.00), then actual dates on the correct order, as does LibreOffice and then all those #N/A fields are a the end.
sorting a column by date wwworks if dates are all same year. sorting dates that span more than one year does not sort. really, people... this is a release version? and a very basic necessary function and it doesn't work???
(In reply to Elmer from comment #8) > sorting a column by date wwworks if dates are all same year. sorting dates > that span more than one year does not sort. > really, people... this is a release version? and a very basic necessary > function and it doesn't work??? Just tested with LO 5.2 nightly version 2.3.2016 build on Windows 7 SP1 and noticed interesting behaviour. Before saving sorting on the Summary tab does not make difference between empty and non-existing (#N/A) date information but once saved as ODS and re-opened, then working as expected, i.e. #N/A sorted last and empty dates at the beginning, existing dates in between and even different years seems to sort nicely.
Ari: can this be closed as WORKSFORME? You are happy?
No,I am not happy. Sorting still works diffetently before and after the Save. After Save it works as expected but before #N/A and zero values are treated equally,
(In reply to Ari Latvala from comment #9) > Just tested with LO 5.2 nightly version 2.3.2016 build on Windows 7 SP1 and > noticed interesting behaviour. Before saving sorting on the Summary tab does > not make difference between empty and non-existing (#N/A) date information > but once saved as ODS and re-opened, then working as expected, i.e. #N/A > sorted last and empty dates at the beginning, existing dates in between and > even different years seems to sort nicely. #N/A is sorted last and empty dates at the beginning for me in attachment 121289 [details] before saving. Is there something I'm missing? Maybe you need to provide a clean file in addition to your "end result" file? Arch Linux 64-bit, KDE Plasma 5 Version: 5.3.0.0.alpha1+ Build ID: cd7b306e614a938b7b39a66784899c22ee6f6c64 CPU Threads: 8; OS Version: Linux 4.8; UI Render: default; VCL: kde4; Locale: fi-FI (fi_FI.UTF-8); Calc: group Built on October 25th 2016
Problem is that sorting works differently (incorrectly) right after the "Paste special" before Save, when those #N/A fields are still "Error: Value not available" results (green, aligned to right) and not differentiated on the sort from the empty cells but sorting works correctly after saving and re-opening file, because they have changed at that point to normal text (black, aligned left).
Actually this is a combination of several problems.. 1) pasting formula error results as values creates an internal formula cell to preserve the error value (as can be seen by the "highlight" green value) 1a) this does not have a leading '=' though 2) such error values seem to be not treated correctly during sort; actually they should be sorted before other values, just as when sorting the original formula range 3) when saved, due to 1a the formula is stored without the leading '=' but cell type string, extended type error, which so far is correct 4) when loaded, this combination results in a string being set at the cell, which is not an error value and thus sorts differently 5) VLOOKUP may return empty cells *for display only*, but these when pasting as values will be pasted as 0 values and if formatted as date result in the null date 1899-12-30 6) similar, they are stored as an empty but 0 formatted value thus when reloading in this case display as null date
(In reply to Eike Rathke from comment #14) > 2) such error values seem to be not treated correctly during sort; > actually they should be sorted before other values I messed that up, errors should be sorted to the end, after any other values (numeric,text,...) and before blank cells.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=c802fb24368ac9866940f2d987d46357b0e577f3 tdf#96475 sort error result between text and empty cell It will be available in 5.3.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=c4820366710fcbcc198a5471ad1f4a29f71d2019 tdf#96475 PutFormulaCell: any other cell than formula is utter nonsense It will be available in 5.3.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=87d2c012ef51aca8bba127145c97409506217a4f tdf#96475 PasteSpecial EmptyDisplayedAsString as empty It will be available in 5.3.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=83cbbc6d664d949f6405409713c5bda1a5be559f tdf#96475 restore the EmptyDisplayedAsString condition during load It will be available in 5.3.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.