Generate a list of unique items from list

[danix]

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)

[joshua4]

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)

[danix]

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

[joshua4]

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.

[Ideafix]

@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.