Bug#757957: marked as done (libreoffice-calc: VLOOKUP() fails when parenthesis present is text)
Your message dated Sun, 6 Mar 2016 22:51:21 +0100
with message-id <56DCA659.4090404@free.fr>
and subject line Re : Bug #757957 - libreoffice-calc: VLOOKUP() fails when parenthesis present is text
has caused the Debian Bug report #757957,
regarding libreoffice-calc: VLOOKUP() fails when parenthesis present is text
to be marked as done.
This means that you claim that the problem has been dealt with.
If this is not the case it is now your responsibility to reopen the
Bug report if necessary, and/or fix the problem forthwith.
(NB: If you are a system administrator and have no idea what this
message is talking about, this may indicate a serious mail system
misconfiguration somewhere. Please contact owner@bugs.debian.org
immediately.)
--
757957: http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=757957
Debian Bug Tracking System
Contact owner@bugs.debian.org with problems
--- Begin Message ---
- To: submit@bugs.debian.org
- Subject: [libreoffice-calc] VLOOKUP() fails when parenthesis present is text
- From: Bzzzz <lazyvirus@gmx.com>
- Date: Tue, 12 Aug 2014 20:16:34 +0200
- Message-id: <20140812201634.0aab7a26@msi.defcon1>
Package: libreoffice-calc
Version: 1:4.3.0-2
Severity: normal
--- Please enter the report below this line. ---
Hi folks,
I'm working on zipcodes from a csv file (structure: zip;town, order by
zip). My goal is to de-duplicate zipcodes numbers and towns names for
further integration into a database.
For towns, I use:
VLOOKUP(town name; array=town names+order columns; 2; 0)
which returns me the row number of the town's name first occurrence
in its list.
When town's name has parenthesis, VLOOKUP() returns #N/A instead of the
wanted result:
TOWN ORDER DE-DOUBLON
…
'ARUE' 38875 16686
'FAAA (AEROPORT)' 38876 #N/A
'TETIAROA (ILE)' 38877 #N/A
'FAAA (PUURAI)' 38878 #N/A
'FAAA (DOMICILES)' 38879 #N/A
'HITIAA' 38880 38880
'MAHAENA' 38881 38881
'PAPENOO' 38882 38882
'TIAREI' 38883 38883
'MAHINA (SAUF OROFARA)' 38884 #N/A
'OROFARA' 38885 38885
…
I don't think this is a wanted behavior (well, at least for me).
BTW, when I changed: …(ILE) to: …[ILE] in the reference column
for test (one line only), it hanged AT LEAST FIVE MINUTES before
giving hand back! (w/ AutoCalculate still off:(
100% of one core during this time (CPU: i5-4210M w/ 4GB RAM).
Sheet is ~40,000 lines, 11 cols.
It took about the same time to recalculate (F9), and the same
when back to …(ILE).
Result: either () or [] triggers the same #N/A.
LO multi-core processing would be very welcome.
--- System information. ---
Architecture: amd64
Kernel: Linux 3.14-2-amd64
Debian Release: jessie/sid
500 unstable www.deb-multimedia.org
500 unstable ftp.fr.debian.org
500 trusty ppa.launchpad.net
--- Package information. ---
Depends (Version) | Installed
=================================================-+-=====================
libreoffice-base-core (= 1:4.3.0-2) | 1:4.3.0-2
libreoffice-core (= 1:4.3.0-2) | 1:4.3.0-2
lp-solve (>= 5.5.0.13-5+b1) | 5.5.0.13-7+b1
libboost-iostreams1.55.0 | 1.55.0+dfsg-2
libc6 (>= 2.14) | 2.19-7
libgcc1 (>= 1:4.1.1) | 1:4.9.1-5
libicu52 (>= 52~m1-1~) | 52.1-5
liblcms2-2 (>= 2.2+git20110628) | 2.6-3
libmwaw-0.3-3 | 0.3.1-2
libodfgen-0.1-1 | 0.1.1-2
liborcus-0.8-0 | 0.7.0+dfsg-4
librevenge-0.0-0 | 0.0.1-3
libstdc++6 (>= 4.9) | 4.9.1-5
libwps-0.3-3 | 0.3.0-2
libxml2 (>= 2.7.4) | 2.9.1+dfsg1-4
uno-libs3 (>= 4.3.0~alpha) | 4.3.0-2
ure | 4.3.0-2
zlib1g (>= 1:1.1.4) | 1:1.2.8.dfsg-1
fontconfig | 2.11.0-5
fonts-opensymbol | 2:102.6+LibO4.3.0-2
libreoffice-common (>> 1:4.3.0) | 1:4.3.0-2
ure (>= 4.2~) | 4.3.0-2
libatk1.0-0 (>= 1.12.4) | 2.12.0-1
libboost-date-time1.55.0 | 1.55.0+dfsg-2
libc6 (>= 2.16) | 2.19-7
libcairo2 (>= 1.2.4) | 1.12.16-2
libclucene-contribs1 (>= 2.3.3.4) | 2.3.3.4-4
libclucene-core1 (>= 2.3.3.4) | 2.3.3.4-4
libcmis-0.4-4 (>= 0.4.0) | 0.4.1-7
libcups2 (>= 1.4.0) | 1.7.4-4
libcurl3-gnutls (>= 7.16.2) | 7.37.1-1
libdbus-1-3 (>= 1.0.2) | 1.8.6-1
libdbus-glib-1-2 (>= 0.78) | 0.102-1
libeot0 | 0.01-3
libexpat1 (>= 2.0.1) | 2.1.0-6
libexttextcat-2.0-0 (>= 2.2-8) | 3.4.4-1
libfontconfig1 (>= 2.11) | 2.11.0-5
libfreetype6 (>= 2.3.5) | 2.5.2-1.1
libgcc1 (>= 1:4.1.1) | 1:4.9.1-5
libgdk-pixbuf2.0-0 (>= 2.22.0) | 2.30.7-1
libgl1-mesa-glx | 10.2.4-1
OR libgl1 |
libglew1.10 (>= 1.10.0) | 1.10.0-3
libglib2.0-0 (>= 2.15.0) | 2.40.0-3
libglu1-mesa | 9.0.0-2
OR libglu1 |
libgraphite2-3 (>= 1.2.2) | 1.2.4-3
libgtk2.0-0 (>= 2.24.0) | 2.24.24-1
libharfbuzz-icu0 (>= 0.9.18) | 0.9.34-1
libharfbuzz0b (>= 0.9.18) | 0.9.34-1
libhunspell-1.3-0 (>= 1.3.3) | 1.3.3-2
libhyphen0 (>= 2.7.1) | 2.8.7-3
libice6 (>= 1:1.0.0)
--- End Message ---
--- Begin Message ---
Hello Bzzzz,
Tue, 12 Aug 2014 20:16:34 +0200, wrote :
>
> I'm working on zipcodes from a csv file (structure: zip;town, order by
> zip). My goal is to de-duplicate zipcodes numbers and towns names for
> further integration into a database.
>
> For towns, I use:
> VLOOKUP(town name; array=town names+order columns; 2; 0)
> which returns me the row number of the town's name first occurrence
> in its list.
>
> When town's name has parenthesis, VLOOKUP() returns #N/A instead of >
> the wanted result:
Upstream responded :
> The name "table" is defined to use relative references. Originally
> defined on Sheet1_2.M35 it references B15:C38. Using the name in
> another cell shifts relative references accordingly, in this case it
> even wraps them around the sheet border. Define the name to use
> absolute references instead, i.e. $B$15:$C$38
>
> There's a bug in displaying a wrapped reference and it displays with
> #REF parts, invoke Manage Names (Ctrl+F3) on cell M35 to get the
> correct value and edit it.
Regards,
--
Stéphane Aulery
--- End Message ---
Reply to: