Generate a list of unique items from list

English
#1

I want to create a list from another list. The list 1 where the values come from looks like this:
name | 0,15 | text | 0,60 | 500
name | 5 | text | 0,30 | 100
name2 | 20 | text | 0,60 | 5000

What I’d want to get is an automated system where that generated list automatically updates as a I add more lines to list 1:
name | 600 ← sum of 4th column: 500+100
name2 | 5000

After adding line:
name | 5 | text | 0,25 | 200

The generated list would be:
name | 800 (600+200)
name2 | 5000

After adding line:
name3 | 55 | text | 0,25 | 200

The generated list would be:
name | 800 (600+200)
name2 | 5000
name3 | 55 | text | 0,25 | 200

It is basically a stock market list where I list stocks I bought, price when bought etc… and then combine all that into a list with all purchases of the same stock is combined in one line. I hope you understand.

I have made a sample calc file.
Untitled 1.ods (12.2 KB)

#2

Thank you for posting an example spreadsheet.

Since you seem to be just getting into more sophisticated uses of Calc, perhaps now is the time that you should instead get into more uses of Base, the database program that comes with LibreOffice.

In the end, what you want is a database system, not a spreadsheet. The current versions of MS Excel have something called “spilling” that would very easily accommodate your totals for each stock name, based on the UNIQUE() and SUMIF() functions. I’m not sure where the very latest version of Calc is at on spill-like behaviors, but I have attached an example modification of your spreadsheet that uses LO Calc. The trick that this example does NOT do is find the latest value among the values. I thought a response for your request as a comment was worthwhile, though, because I do use UNIQUE() and SUMIF() all the time in daily work.

Also, I don’t see the real value of a sum of changing prices, so I’ve included use of the AVERAGEIF() function as well.

Untitled 1.ods (17.6 KB)

#3

I have to say I don’t really understand how it works? Is this for excel? Thank for response though.

#4

The attachment is for LibreOffice Calc. The download is an ODS (Calc) file. I mention Excel because it does similar things with a little more power behind it, and you will run across Excel solutions doing web searches, etc. Mostly, Excel “spills,” which Calc does not. That is, Excel can adjust for different numbers of unique items, while Calc cannot, except when you first enter the UNIQUE() formula.
~
In other words when you use the UNIQUE() function, you simply cite the range and Calc pulls out one each of each different item in that range. So UNIQUE(A1:A15) will create an array formula of all of the different items in that A1:A15 range. Let’s say there are 7 of them. So Calc will create an array formula that fills in seven rows, one row for each different item.
~
Normally an array formula is a formula that is entered using Ctrl+Shift+Enter instead of just Enter. You mark the range the array function will be in first, then enter the formula, then press that ‘magic’ key combination. Formulas like UNIQUE(), however, will automatically set themselves up as array formulas and expand the selection to just large enough to fit the current answers–here that is seven rows. Once done, however, this won’t ever change. So if there come to be more answers (more unique items, say 9 unique items), then those will not appear in the unique item list…they won’t get added to the bottom because the array formula is fixed at the original seven rows.
~
So, on the second sheet, UNIQUE(…) was entered by marking the entire colored range first, then typing in the =UNIQUE(…) then pressing Ctrl+Shift+Enter. That way there were extra spots incorporated into the range of answers, so if more, different items are added to the list on the first sheet, then they will appear on the second sheet. However, wherever there is currently no item you see the #N/A.
~
The SUMIF() and AVERAGEIF() functions on the second sheet are normal functions that refer to the individual cell entries in the unique item list, then do their thing based on that cell entry and the range of the stock values on the first sheet.

1 Like
#5

@joshua4
The UNIQUE function can add new rows to the generated array as you insert new items in the source range if you set the UNIQUE function to evaluate the entire column instead of a specific range.
In your example, instead of UNIQUE(A1:A15) try using UNIQUE(A:A) and it will add new entries to the generated array.

#6

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

#7

Perhaps the ISNA() function might be useful.

#8

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.

#9

the ssue is tdf#127808 and siblings, sofar no solution!

#10

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.

#11 
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!

#12

One way is here.

#13

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,))
#14

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
#15

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.

#16

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!

#17

What’s wrong with my TestSetArrayFormula ?

#18

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!

#19

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.

#20

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!!«

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.