Date: prev next · Thread: first prev next last
2017 Archives by date, by thread · List index


Thanks. That explains everything.

On 06/10/17 08:23 PM, Remy Gauthier wrote:
Hi,

I think your issue comes from the changes introduced in Version 5.3. The V5.3 release notes (https://wiki.documentfoundation.org/ReleaseNotes/5.3#Calc) say the following:


      Option settings

  * In new installations the default setting for new documents is now
    *Enable wildcards in formulas* instead of regular expressions.
    tdf#88581
    <https://bugs.documentfoundation.org/show_bug.cgi?id=88581> (Eike
    Rathke (Red Hat, Inc.))
      o This is for better interoperability with other spreadsheet
        applications and a better experience for users who are not
        familiar with regular expressions. Wildcards are more
        widespread and the large set of regular expression
        metacharacters often makes queries too complicated for casual
        users.
      o See also ReleaseNotes 5.2 for wildcards
        
<https://wiki.documentfoundation.org/ReleaseNotes/5.2#Support_wildcards_to_be_compatible_with_XLS.2FXLSX_and_with_ODF_1.2>.


If you go to Tools -> Options -> Calc -> Calculate and enable "Enable regular expressions in formulas", it should work (at least, it does for me). You can also change your ".*" to just "*" to make it work (but that will prevent the other spreadsheet from perhaps working correctly).

If you want to be more robust, you could use a dedicated worksheet that has a small array of lets say 1 columns and 6 rows. In column 1 (starting at A1), you place a, b, c, d a, e, f and in column 2 (starting at B1), you place 1, 2, 3, 4, 5, 6, like this:

a 1
b 2
c 3
d a 4
e 5
f 6

Create a named cell (for instance "Wildcard" without the quotes) that contains this formula:

=IF(ISERROR(VLOOKUP("d"&"*",A1:B6,2,0)),".*","*")

You can then use something like this as your search element the lookup function: UPPER(TRIM(C2)&" "&TRIM(E2)&Wildcard

I tried to use an inline matrix instead of A1:B6 but I did not get any good results. If anyone has an idea, I would be interested in knowing how to do it (it would require only one cell instead of 7 (6 for the array, 1 for the result)).

I hope this helps.

Rémy Gauthier.

Le vendredi 06 octobre 2017 à 18:46 -0400, Gary Dale a écrit :
I have two spreadsheets. I created the first back in June. It uses two
sheets, one for registration information (Entrants) which I download
from a site that takes registrations for races, and the other (Results)
I download from a site that the timer posts results to. The second sheet
has the standing information while the first has all the entrant
information. I can relate the two by the entrant's name, which is 3
columns in the first sheet and 1 (uppercase) column in the second using
the formula:

=N(VLOOKUP(UPPER(TRIM(C2)&" "&TRIM(E2)&".*"),Standings.C$2:J$37,8,0))

where C & E are the first and last name columns on Entrants, Standings.C
contains the combines first and last names and column J is the number I
want (standing within a division by age and gender). A failed lookup
would mean the person registered but didn't complete the event.

This works in the spreadsheet I created in June but doesn't work in the
spreadsheet I just created. I traced this down to the &".*" part of the
search term. This was intended to handle trailing spaces or other
extraneous characters after the person's name. The spreadsheet I created
in June didn't have any but the one I just created did. However removing
the trailing spaces from the Standings names didn't help. The formula
simply didn't work.

Replacing the &".*" with a simple &" " to assume that the names always
have a trailing space did work, as did removing the trailing spaces and
leaving out the &" ". However the original formula works on the June
spreadsheet with or without trailing spaces in the names.

I'm working with both spreadsheets on the same computer using the same
copy of LibreOffice Calc (5.4.1.2.0+ on a Debian Buster AMD64 system). I
can have them open side by side to demonstrate the problem (it's also
how I copied and pasted the formula between the two - multiple times -
to ensure that it wasn't a typing error).

Does anyone have any ideas on what could be going wrong?




--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Context


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.