Syntax help conditional copy row to sheet

[IWebb]

Hi

I am still new to calc and function syntax. I am trying to copy a row to a sheet based on a condition. I have followed the steps from this post which is copying everything which is a good start.

• Calc: If a field contains data, copy the entire row to another worksheet
  Now I just need to adjust the formula to only copy the row if a condition is met. I am getting stuck here.

=IFERROR(T1+MATCH(TRUE(),""<>OFFSET($Master.$A$1,T1,0,COUNTA($Master.A:A)-T1,1),0),"") i.e. how do I apply a condition to the formula?

In the linked file I need to copy from the Master sheet to another sheet if column c matches the sheet name.

I have set up the sheet Master & ActionBet with sample data to get help with this. If we could get actionbet sheet working i could then produce the same on all other sheets.

So if $Master.C:C = “ActionBet” copy row to $ActionBet , if $Master C:C = “BBet” copy row to $Bbet etc etc

Many Thanks
Isaac

Hopefully this upload will work

MySample.ods (22.8 KB)

edit Fake.ods removed by @karolus

[karolus]

Hallo

def filter_c_to_sheet(*_):
    doc = XSCRIPTCONTEXT.getDocument()
    sheets = doc.Sheets
    master = sheets['Master']
    cursor = master.createCursor()
    cursor.gotoStartOfUsedArea(False)
    cursor.gotoEndOfUsedArea(True)
    head, *crit_source = cursor[:,2].DataArray #Column C
    criteria = sorted(set(list(zip(*crit_source))[0])) 
    for key in criteria:
        if not sheets.hasByName(key):
            sheets.insertNewByName(key, sheets.Count)
        crit = sheets[key]['C1:C2']
        crit.DataArray = [[head[0]],[key]]
        descriptor = crit.createFilterDescriptorByObject(cursor)
        descriptor.CopyOutputData = True
        descriptor.OutputPosition = sheets[key]['A1'].CellAddress
        cursor.filter(descriptor)

please test the attached Document with embedded pythonscript.
sample_with_python_script.ods (23.6 KB)

Use apso.oxt from here to organize python-code

[IWebb]

Hi thanks for your help
It worked fine in the sample
When I try to put it in my main file I am getting an error

(Error during invoking function filter_c_to_sheet in module vnd.sun.star.tdoc:/6/Scripts/python/Module.py (<class ‘uno.com.sun.star.uno.RuntimeException’>:
File “C:\Program Files\LibreOffice\program\pythonscript.py”, line 915, in invoke
ret = self.func( *args )
File “vnd.sun.star.tdoc:/6/Scripts/python/Module.py”, line 12, in filter_c_to_sheet
))

I don’t really understand scripts and macros sorry
So from what I understand -

1. I installed the apso.oxt extension then restarted LO and the organise python scripts is now under macros.
2. I made a python file with VS Codium copied your code then tried to run it.
3. I get an error

If I look at line 12 in the code I already have all the sheets created is this what is going wrong?
Any further help is appreciated

[karolus]

i can reproduce the same Error_message with at least one blank cell in Column C inside the Source-data, you may correct the content of ColumnC … or otherwise ignore blank cells with:

    for key in criteria:
        if not key:
            continue
        if not sheets.hasByName(key):

by inserting the 2 lines in the middle.

[IWebb]

@karolus

    for key in criteria:
        if not key:
            continue
        if not sheets.hasByName(key):

It works now
Big thank you!

[dscheikey]

If someone doesn’t want a solution with Python code, you can also create a kind of filter with AGGREGAT. In combination with two named areas, this works quite well.

Sheetname = RIGHT(CELL("Filename",$A$1),LEN(CELL("Filename",$A$1))-SEARCH("#$",CELL("Filename",$A$1))-1)

and

Data = $Master.$A$1:$M$31

you can use the following formula in A2 and copy it to the right and down.

=IFERROR(INDEX(Data,AGGREGATE(15,6,(ROW(Data))/(Data=Sheetname),ROW(1:1)),0),"")

MySample.ods (33,7 KB)

[karolus]

oh yes…python is a dangerous beast, avoid it no matter the cost

btw.

sheetname = MID(CELL("FileName");FIND("#$";CELL("filename"))+2;99)

[dscheikey]

Don’t take it personally. I love Python and am glad that you were able to offer the user a great solution. I don’t want to dispute your solution. It has already been marked.
I had already started with the suggestion yesterday, but then didn’t finish (too tired). I didn’t want to waste my work, so I included it as a supplementary possibility.

Should I delete it just so that everyone only uses macros and doesn’t get to know the possibilities of the functions. That would be stupid too.

[IWebb]

Thanks for this, I really appreciate it. I can see myself using this strategy in the future and its good to know that I don’t need to use macros which I cant alter because I don’t understand the language.