goosepirate / lox365 Goto Github PK

What im doing wrong?
This is the sheet

There is the formula

=FILTER(A4:G32,A4:A10=I3,"")
What can i do?
Thanks.

Ver.4
libreoffice 7.3 and 7.4
install this ext.cannot install . this daialog messege.

(com.sun.star.uno.RuntimeException) { { Message = "<class 'TypeError'>: 'type' object is not subscriptable, traceback follows\X000a File "C:\Program Files\LibreOffice\program\pythonloader.py", line 147, in writeRegistryInfo\X000a mod = self.getModuleFromUrl( locationUrl )\X000a File "C:\Program Files\LibreOffice\program\pythonloader.py", line 102, in getModuleFromUrl\X000a exec(codeobject, mod.dict)\X000a File "C:\Users\baian\AppData\Roaming\LibreOffice\4\user\uno_packages\cache\uno_packages\lu281968eed7m.tmp_\Lox365 (2).oxt\loader.py", line 6, in \X000a class Lox365(unohelper.Base, XLox365):\X000a File "C:\Users\baian\AppData\Roaming\LibreOffice\4\user\uno_packages\cache\uno_packages\lu281968eed7m.tmp_\Lox365 (2).oxt\loader.py", line 9, in Lox365\X000a def _get_dataarray(self, cellrange, positions: dict) -> tuple[tuple]:\X000a\X000a", Context = (com.sun.star.uno.XInterface) @0 } }

XLOOKUP("E", A:A, B:B) is very slow as compared to XLOOKUP("E", A2:A4, B2:B4).

I think the code is iterating through each row until the MAX possible row, rather than identify the last used row, for a match.

Hello,
The reason I want to use XLOOKUP is to perform a reverse search starting at the last item, so I modified your code to add that part. I've only added it in the vertical direction but hopefully this will help you make a version that works for vertical too. I also didn't add the binary search modes. Here is the modified code for the lox365.py file:

def XLOOKUP(lookupValue, lookupArray, returnArray, ifNotFound=ERR_NA, searchMode=1):
    if ifNotFound is None: ifNotFound = ERR_NA
    if searchMode is None: searchMode = 1
    lookup_direction = 0 # 0 is vertical; 1 is horizontal
    if len(lookupArray) == 1 and len(lookupArray[0]) > 1: lookup_direction = 1
    try:
        if searchMode == -1:
            if lookup_direction == 0:
                return (returnArray[len(returnArray) - lookupArray[::-1].index((lookupValue,)) - 1],)
            if lookup_direction == 1:
                return tuple((row[lookupArray[0].index(lookupValue)],) for row in returnArray)
        else:
            if lookup_direction == 0:
                return (returnArray[lookupArray.index((lookupValue,))],)
            if lookup_direction == 1:
                return tuple((row[lookupArray[0].index(lookupValue)],) for row in returnArray)
    except ValueError: return ((ifNotFound,),)

Here is the added node to the addin.xcu file:

            <node oor:name="searchMode" oor:op="replace">
              <prop oor:name="DisplayName"><value xml:lang="en">[Search mode]</value></prop>
              <prop oor:name="Description"><value xml:lang="en">The order of the search. 1=Start from first item (default) -1=Reverse.</value></prop>
            </node>

Hi.

I've found a bug.

I've installed this wonderful extension today, but when I enable "Use English function names" in Calc settings (which I really need) every lox365 function stops working and gives out #NAME? error.

Please, advise.

Edit. Found a bypass but suboptimal:
added COM.GOOSEPIRATE.LOX365.OXT. before every function name ie. COM.GOOSEPIRATE.LOX365.OXT.XLOOKUP and all is well even if "Use English function names" is activated.
it is suboptimal, though.

I'm trying to use a value in a cell D1 as filtering value.

The image above shows the result for value 4. the next ones show the result for 3, 2 and 1.

What is the correct approach to filter a list by a value that might change?

BTW - if I expand the list A2:B4 to something bigger the FILTERed list doesn't expand - I need to delete and create it from scratch.

As title says---

Hi, I'v downloaded en installed the extension in LO 7.6.0.3. Function SORT is available, seems to work when selected an array, But when entered, it displays only one cel (the first one), Something is wrong with [sort index] in my case. It doesn't accept any value for colomn/row. Any idea what is wrong or what I'm doing wrong?
Thanks for the feature anyway!

Edit when used UNIQUE i got an similar issue. Only one value is displayed.
kind regards

Hi! Please, explain, how these functions work? Eg, I am trying to select the range, use as the argument, but the data are not populated automatically, only the first upper cell. Thanks.

Currently trying to install the extension on LibreOffice 7.3.7.2 30(Build:2) yield the following error on installation.
I tried restarting the app but the functionalities are not added.

Could you help fix this issue plase?

Hi Goosepirate

Would you consider adding another LibreOffice Calc function, ReverseText, which reverses a text string? So ABC becomes CBA.

Use case for this is explained in this thread: https://twitter.com/Tagishsimon/status/1371384451754778627 ... should say that twitter does not do a good job of displaying the whole thread; there are about 10+ tweets explaining the need; clicking on the bottom tweet will force twitter to display the next tweet.

thanks
simon

Using a 2nd XLOOKUP as the [if not found] argument does not appear to work.

XLOOKUP( value, lookup1, return1, XLOOKUP( value, lookup2, return2, "not found" ) )

Thanks Goose for your work on this extension! I've been trying to use this but look like the FILTER function doesn't work. Are you able to provide an example file that's working, or an example formula?

I've been troubleshooting this for the last 3 hours...lol. Looked on google and try many different variations of the Excel FILTER function but always returning no results.

Hello,
I try to use the function to output multiple rows. As a return array I entered a two-dimensional array (e.g. B3:C9), but as result I just get one value from the one dimensional array in column B.

Hello.

Please write release notes for each release, for example version 5.0

Thank you.

SORT() function, sort numbers as if they were string,
1
2
11
returns
1
11
2