Download it now!
Bug 105414 - Blank cells formatted as numbers not treated as ZERO when pulled via VLOOKUP to external file
Summary: Blank cells formatted as numbers not treated as ZERO when pulled via VLOOKUP ...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.2.4.2 release
Hardware: x86-64 (AMD64) Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Function-Vlookup
  Show dependency treegraph
 
Reported: 2017-01-18 15:02 UTC by Charles
Modified: 2020-07-26 03:52 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Source spreadsheet example (10.99 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-01-18 15:04 UTC, Charles
Details
Management spreadsheet example (11.61 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-01-18 15:05 UTC, Charles
Details
Management Example Spreadsheet (11.61 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-01-19 13:36 UTC, Charles
Details
Screenshot of sort in Management sheet (37.74 KB, image/png)
2017-01-19 13:37 UTC, Charles
Details
Screenshot of Sort in Source (32.53 KB, image/png)
2017-01-19 13:43 UTC, Charles
Details
The two separate files combined into one (11.59 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-01-20 15:13 UTC, Charles
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Charles 2017-01-18 15:02:16 UTC
Description:
I have a spreadsheet - 'A' that has data entered into it on a regular basis. The cells this data is entered into are formatted as Number>Currency (with no decimals).

I have another spreadsheet - 'B' that Management uses that pulls some data from spreadsheet 'A' using VLOOKUP.

When sorting the Source (A) spreadsheet in descending order, the blank cells are treated as zeros, so cells with values in them are sorted to the top, and all blank cells are sorted below them.

But, when I sort the cells in the Management spreadsheet (B), the blank cells are sorted to the top, above the cells with values in them, and then those with values are properly sorted in descending order.

I consider this a bug.



Steps to Reproduce:
1. Create a spreadsheet - Source - with two columns, column A that has labels in it, and column B that has numeric data (formatted as Currency with no decimals) entered into some of the cells but not all.

2. Create a second spreadsheet - Management - identical to 'Source', but pulls the data for column B from column B in 'Source' spreadsheet using VLOOKUP on column A.

3. Sort Source spreadsheet on column B in descending order, note that the blank cells are treated as zeros, so cells with values in them are sorted to the top, and all blank cells are sorted below them.

Actual Results:  
4. Sort Management spreadsheet the same way, note that the blank cells are sorted to the top, above the cells with values in them, and then those with values are properly sorted in descending order.

Expected Results:
The step 4. sort should be identical to the step 3. sort.


Reproducible: Always

User Profile Reset: No

Additional Info:


User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:50.0) Gecko/20100101 Firefox/50.0
Comment 1 Charles 2017-01-18 15:04:16 UTC
Created attachment 130527 [details]
Source spreadsheet example

This is an example Source spreadsheet that, when used with the Management example also attached, exhibits the problem.
Comment 2 Charles 2017-01-18 15:05:38 UTC
Created attachment 130528 [details]
Management spreadsheet example

This is an example Management spreadsheet that, when used with the Source example also attached, exhibits the problem.
Comment 3 Charles 2017-01-18 15:07:13 UTC
Sorry for the conflicting names in the description vs the Steps to Reproduce - I decided to change the names of the spreadsheets while writing up the Steps to Reproduce when I realized that referencing the columns as A and B would be confusing if the spreadsheets were named A and B.
Comment 4 m.a.riosv 2017-01-18 22:35:11 UTC
Works fine for me, changing the name of the source file to A.ods

Version: 5.2.5.1 (x64)
Build ID: 0312e1a284a7d50ca85a365c316c7abbf20a4d22
CPU Threads: 4; OS Version: Windows 6.19; UI Render: GL; 
Locale: es-ES (es_ES); Calc: group
Comment 5 Charles 2017-01-19 13:36:23 UTC
Created attachment 130555 [details]
Management Example Spreadsheet

Oops, forgot to change the reference in the spreadsheet before uploading.
Comment 6 Charles 2017-01-19 13:37:32 UTC
Created attachment 130556 [details]
Screenshot of sort in Management sheet

Screenshot of what it looks like sorted on column B in descendning order in the management sheet
Comment 7 Charles 2017-01-19 13:43:00 UTC
Created attachment 130557 [details]
Screenshot of Sort in Source

And this is what it looks like after sorting on column B in descending order in  the original/source sheet
Comment 8 Charles 2017-01-19 13:44:31 UTC
(In reply to m.a.riosv from comment #4)
> Works fine for me, changing the name of the source file to A.ods
> 
> Version: 5.2.5.1 (x64)
> Build ID: 0312e1a284a7d50ca85a365c316c7abbf20a4d22
> CPU Threads: 4; OS Version: Windows 6.19; UI Render: GL; 
> Locale: es-ES (es_ES); Calc: group

Thanks for pointing out I forgot to change the reference after I changed the filenames... :)

But - when you say 'works fine', are you saying you do not see the blank cells ABOVE the numeric ones in the Management sheet when sorted in DESCENDING order?

I just attached screenshots of what the exact same sort looks like in each sheet.
Comment 9 m.a.riosv 2017-01-19 16:02:02 UTC
Sorting on Source doesn't matter for VLOOKUP() on Magnagement, last parameter is 0.

Sorting on Magnagement only column B changes nothing for me. But by your image you are sorting also the column A with column B values base, see the letters on the last rows, so there is nothing wrong on the results.

I think the better you can do is to create a pivot table from the magnagement A:B table, in which you can sort and do some calculation without need to touch the link formulas.
Comment 10 Charles 2017-01-19 17:52:49 UTC
Sorry, I don't understand your explanation.

Of course I also included column A data in both sorts, otherwise the relationship with the other data in the row is scrambled. My apologies for not including this in the steps to reproduce the problem, I thought it was obvious.

Highlight all data in both columns A and B, then sort on column B, and the sorts are very different when doing it from the Management sheet using VLOOKUP.

So, please provide documentation or at least an explanation on why the use of VLOOKUP causes the blank cells to NOT be treated as zero, as they are in the Source sheet, or confirm this as a bug.

Thanks
Comment 11 m.a.riosv 2017-01-19 21:55:00 UTC
Once the obvious explained I can see the issue.

Looks the same problem than in tdf#103230, empty cells from linked files are not interpreted the same than those on the same file.

*** This bug has been marked as a duplicate of bug 103230 ***
Comment 12 Charles 2017-01-20 15:11:35 UTC
No, not a duplicate of that one, using VLOOKUP when the two sheets are in t he same file, as is the case in the bug you duped this one to, works just fine.

I will attach a 'Combined' example (just copied the management sheet to the source book and adjusted formulas) to demonstrate.
Comment 13 Charles 2017-01-20 15:13:02 UTC
Created attachment 130579 [details]
The two separate files combined into one

Copied the Management sheet into the Source spreadsheet, and adjusted the reference, to demonstrate that it works just fine when the formula is calling data from the same spreadsheet.
Comment 14 Buovjaga 2017-02-03 08:42:38 UTC
NEW per Miguel's confirmation.
Comment 15 QA Administrators 2018-07-26 02:41:29 UTC Comment hidden (obsolete)
Comment 16 QA Administrators 2020-07-26 03:52:11 UTC
Dear Charles,

To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.

There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.

If you have time, please do the following:

Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the information from Help - About LibreOffice.
 
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice.

Please DO NOT

Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not 
appropriate in this case)


If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/

2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to 'inherited from OOo';
4b. If the bug was not present in 3.3 - add 'regression' to keyword


Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa

Thank you for helping us make LibreOffice even better for everyone!

Warm Regards,
QA Team

MassPing-UntouchedBug