I was looking into this function and found you response helpful. However the N/A is annoying and least appealing. do you know of a way to eliminate them. Possibly conditional formatting? I look forward to your response
Thanks
Please attach a sample file, reduce the size as much as possible without private information,
and paste the information in Menu/Help/About LibreOffice, there is a copy icon.
the ssue is tdf#127808 and siblings, sofar no solution!
It’s currently impossible to change the size of a dynamic array in automatic formula recalculation mode.
It’s probably possible to recalculate array formulas with redefined output ranges using a macro.
def remove_NA_from_Arrays(*_):
"""
**shrinks** the output-area
to the actually needed size
**todo**: how to **expand** the size if needed??
"""
doc = XSCRIPTCONTEXT.getDocument()
sheet = doc.Sheets[0]
ranges = sheet.queryContentCells(16) #all Formula-ranges
for single in ranges:
if (formula:=single.ArrayFormula): # is it a ArrayFormula?
valid = single.queryFormulaCells(3) # query valid output (1+2)
single.clearContents(16) # clear
valid[0].ArrayFormula = formula[1:-1] # set
read the doc-string!
does it resize to a size greater th actual size if needed?!
from com.sun.star.beans import PropertyValue as pv
def resize_FormulaArrays(*_):
doc = XSCRIPTCONTEXT.getDocument()
frame = doc.CurrentController.Frame
disp = XSCRIPTCONTEXT.ctx.ServiceManager.createInstance("com.sun.star.frame.DispatchHelper")
sheet = doc.Sheets[0]
ranges = sheet.queryContentCells(16) #all Formula[range]
for single in ranges:
if (formula:=single.ArrayFormula): # is there a ArrayFormula?
single.clearContents(16) # clear
arg = pv( Name="ToPoint", Value= single[0,0].AbsoluteName)
disp.executeDispatch(frame, ".uno:GoToCell", "", 0, (arg,))
arg = pv( Name="StringName", Value = formula[1:-1] )
disp.executeDispatch(frame, ".uno:EnterString", "", 0, (arg,))
There is a logical error in your result table.
The names are unique, the numbers are aggregations (sum and max), but the text remains unaggregated. What if any name has different values in the text field?
One row for each combination of name and text?
Count different text values?
Concatenate different text for each name?
Or simply skip the text column?
Using a most simple pivot table skipping the text field, a possible solution looks like this:
ask120487.ods (14.8 KB)
1 Like
If we write the macro from the mentioned link to a new Calc document and run the TestSetArrayFormula macro multiple times, the resulting range will cyclically increase/decrease.
The key is to use the .uno:InsertMatrix command, passing the localized formula text as a parameter.
I had already tried with:
…
arg = pv( Name="Formula", Value = formula[1:-1] )
disp.executeDispatch(frame, ".uno:InsertMatrix", "", 0, (arg,))
but it doesnt expand to the proper size, therefore:
…
arg = pv( Name="StringName", Value = formula[1:-1] )
disp.executeDispatch(frame, ".uno:EnterString", "", 0, (arg,))
works as expected in my Tests!
What’s wrong with my TestSetArrayFormula ?
It works pretty good ……but only in the case where it is trivial to precalculate the size of the Output-range!
Imaging existing Array-formulas with (probably nested) Functions like FILTER(…) … UNIQUE(…) where you dont know in advance the needed size for the output!
But nowhere do I attempt to pre-calculate the size of the output array.
Let’s try it with a specific example that’s beyond the capabilities of my function.
I apologize, the uno:InsertMatrix works the same way also for single cell-arguments.
But test it yourself:
resize_arrays.ods (12.4 KB)
In the linked example you are calculating the Output-range from length of »Hello World!!«
Since decades, a well proven method to create a list of unique items goes like this:
Having a database-like list with one top row of column labels and consistent data below each label, you select the list and call Data>Pivot>Create… In your sample, all you need is a column label on top of your names in order to fulfill this reqirement.
In the upcoming wizard, you drag the name column into the “Row fields” box (one row per item. This creates a pivot table representing the list of unique items from the source column. Data>Pivot>Refresh or “Refresh” from the context menu refreshes this list while the cell cursor is within the pivot table.
In order to include the text field, right-click>Edit recalls the wizard, where you drag the text field into “Row fields”. Now you have a list of all unique combinations of name and text.
Drag “Total values” to “Data fields” and you get a third column showing the sum of that column for each unique combination of name and text.
Drag “Maximum” to “Data fields”, change “sum” to “max” and you get a forth column showing the max of that column for each unique combination of name and text.
In addition to the unique names, @danix wants aggregated numbers for each name.
P.S. and selecting duplicate items does not help when you need a list of unique items. This new feature looks questionable anyway.
1 Like
This is achieved using the Remove action.
Hello, @karolus !
I’ve added a button to your example that recalculates the document formulas and then regenerates the array formula in cell C1. The macro has been modified.
Currently, I don’t see any errors in the calculation results.
resize_arrays_2.ods (17.2 KB)
P.S. Thanks for the examples. I’m learning the Python / UNO combination using your texts (and MRI). 