I have this function on Google Sheet that I’m trying to convert to LibreOffice:
=UNIQUE(ARRAYFORMULA(IF(Transactions!$A2:$A <> "", YEAR(Transactions!$A2:$A), "")))
But I’m unable to find an alternative for LibreOffice.
Input data on Transactions:
6/12/2020 15:42:23
4/19/2021 15:42:23
4/20/2021 16:39:12
4/20/2021 16:39:31
4/27/2021 16:13:10
Output data on Sheet1:
2020
2021
I’m trying to make this dynamic without the need to fill down.
You could use an advanced filter, with option to remove duplicates, and definition of your sheet1 as output destination. You’d have to put the year in a helper cell/column first.
By using refresh range this can be repeated.
Usage shown at libreofficehelp.com
J.
PS Arrayformulas are also availabe in LibreOffice
A
The usage of array formulas in LibreOffice (with a result needing more tthan one cell for the output) requires to lock a fix output range. This reduces its value/usability in cases where a variable size of the output range would be preferrable (like in the UNIQUE-case), and aggravates the maintenance of the sheet (insertion/deletion of rows/columns e.g.). Have Google (their “-Sheets”) means to solve this conceptual problem or do they simply not worry and leave it to the user?
B
Calc hasn’t a UNIQUE function, but if the mentioned issue isn’t relevant for a user, its not difficult To create a sufficiently efficient implementation of it even with Basic code, as long as not the sheet is misused to mimic a database, and has many thosands of data sets.
C
Personally I use a combination of TEXTJOIN() combined with a user function XTEXTSPLIT() for textual cases of the task, but restrict this to a few dozen rows in usage. XTEXTSPLIT() comes with UNIQUE by mode parameter.
No, we still wait any developers for implementing it
See https://bugs.documentfoundation.org/show_bug.cgi?id=126573
Did you also consider my point “A” in the comment on the answer by @Wanderer?
(The enhancement “bug” you linked to got 2 comments in 2 years, the first one (by @erAck ) rather clerly explaining the reasons to not hurry in this case, the other one supporting the idea withou commenting on the issues.)
And? I personally like the new dinamic arrays in MS Excel and its new functions.
It’s easy to do with a pivot table, after create the pivot table with date field, use [F12} Menu/Data/Group&Outline/Group and select only year. But you need to update it manually, and maybe it can help to get other values by year.
Thanks, this is exactly what I needed! Is there a way to remove the (empty) column?
That’s a bit confusing to me concerning the “only year”. I didn’t get a choice, but this functionality was chosen automatically when I created the PT without accompanying data (amounts) while I got a PT listing the 5 different time stamps for the rows when an amount was appended.
Since I disliike unclear automatisms, I dare ask you to help me to understand. See attachment.
ask310898_M.ods
@Zelix, I think there is no way to remove the empty column.
@Lupp, If I understand fine, and as I remember, this happens when a second/… pt is created from the same data range, seems some parts of PTs are connected when they have the same source range. What I don’t know is if it is on purpose or a bug. Changing only a row/column of the source range seems to avoid the problem.
@mariosv: Thanks. However, this seems to be related to a kind of “automatisms out of the dark” I definitely don’t want to rely on. If I should have such a task, I would surley prefer a helper column. If then Calc makes ithe PT differently to what I wanted, its clearly a bug. Much better IMO.
@Lupp, I don’t like this kind of darkness things, neither, that are not in the help, I think I found it, time ago by casualty. Looking in bugzilla I have found my report EDITING: PIVOT TABLE, creating a group in one PT also change the same field in all PT from same data source, informed as NOTABUG by Kohei.
@mariosv: Thanks again.
However, I would judge the behavior of the PT generator you based your answer on to be one of the bugs which can’t be fixed because they got misused as features.
Some may know thousands of “instances” of this bug-class helping a known competitor to lock in their users, and to force free software to implement bugs.
How does the related feature work in Ekscel?
(I didn’t see much from Kohei since the dispute about ‘Update references when sorting’.)
A solution using a helper column and a pivot table is presented and discussed in the attached example.
That’s for completeness only.
The automatic rfresh depends on the permission to run the Basic code included with that example.
ask310898UniqueByPivotTable.ods
See my comment ther4e.