GNOME Bugzilla – Bug 742601
filter: does not filter #N/A
Last modified: 2015-01-09 18:36:55 UTC
I am using v-lookup to compare numbers on 1 sheet to numbers on another sheet. If the number exists on the 1 sheet, it shows up as the number on the other sheet. If a number does not exist it shows up as #N/A on the other sheet. When I add the filter to the other sheet, the drop down shows the numbers, blanks, non-blanks and #N/A as my choices to filter. When I pick #N/A, the sheet goes blank. I want to filter the #N/A's so I can add them to the first sheet. The first sheet has like order numbers that are processed, and the other sheet has all old and new orders. I want to filter the new orders. I have changed the format in the cells where the v-lookup formula is from text numeric and still the same results.
The problem is that when you are trying to compare two error values the answer is always an error value rather than "true". You can filter this by using a custom filter asking for "begins with #N/A".
Andreas: you seem to be thinking NaN semantics. Our error values have plain old equality semantics. In any case, we list error values in the dropdown so selecting them should do something useful. Blank isn't useful.
This problem has been fixed in our software repository. The fix will go into the next software release. Thank you for your bug report. I went through all uses of value_cmp, value_compare, and related functions. Most will benefit from having a defined ordering of errors, which is what this fix does. This includes everywhere we sort values that might be numbers. Some, such as those involved in evaluation, won't notice because they filter errors values early. This is probably why value_compare didn't consider errors until now. For a few it's a coin flip, but probably fine. All tests pass.