Description: There are some array features in spreadsheets which recently have been added to Excel. These functions make lots of sense and they remind me of statistical software (e.g. Stata) which have very similar functions: FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, RANDARRAY https://support.office.com/en-us/article/new-array-functions-003df6c7-1dcb-4388-8e2e-0fe77a0887bc?ui=en-US&rs=en-US&ad=US Steps to Reproduce: . Actual Results: . Expected Results: . Reproducible: Always User Profile Reset: No Additional Info:
Note that those functions aren't fully specified yet, for example https://support.office.com/en-us/article/RANDARRAY-function-21261e55-3bec-4885-86a6-8b0a47fd4d33 says: Note: January 14, 2019: The RANDARRAY function is one of several beta features, and currently only available to a portion of Office Insiders at this time. We'll continue to optimize these features over the next several months. When they're ready, we'll release them to all Office Insiders, and Office 365 subscribers. Also, it needs to be investigated how the spilling situations are actually handled and what happens if due to recalculation the return array size varies. This is not just new spreadsheet functions but a new behaviour that calculating a function can change spreadsheet content. Also restrictions like such formula can be edited only at the top left of its array need to be implemented, though we maybe could handle that differently as long as the edited formula affects the range as if it was edited at the top left. Our current array formula display/edit behaviour already is somewhat similar.
*** Bug 127809 has been marked as a duplicate of this bug. ***
I would like to support Gerry's cause. In my opinion also the XLOOKUP function would be very helpful. It returns an array if needed. XLOOKUP, FILTER, SORT, UNIQUE are my wishes. Juergen
Another vote of support.
These are not just nice to have anymore, some or all of those functions are already used in Google Sheets for a long time and needed for intercompatability, and are not Office Insiders exclusive anymore.
Here is the documentation for the Google Sheet functions: FILTER https://support.google.com/docs/answer/3093197?hl=en SORT https://support.google.com/docs/answer/3093150?hl=en SORTN https://support.google.com/docs/answer/7354624?hl=en UNIQUE https://support.google.com/docs/answer/10522653?hl=en SEQUENCE https://support.google.com/docs/answer/9368244?hl=en RANDARRAY https://support.google.com/docs/answer/9211904?hl=en
Another vore from me.
Interesting applications: https://superuser.com/questions/1711510/when-filtering-data-is-it-possible-to-put-in-a-cell-the-value-of-the-filter https://superuser.com/questions/807286/equivalent-for-filter-formula-in-libre-office
I add my support for these functions. Over the years I've come across the limitations of array handling a number of times. Most often I've found wacky workarounds with compromises on functionality and likely performance. However, there are instances where the limitations are simply too constricting making the ideal handling of arrays impossible. I'll share the example I'm currently facing later, but provide a general use case in favor of the need of these functions. I'll start by saying that dynamic Array manipulation is critical in handling data and has so many use cases. In my opinion the dynamic handling of arrays is the only remaining missing link in both function and form, at least as far as data handling in Calc. One general limitation is that you can't take 2 arrays and interweave the data filtered on criteria, and then sorted to be in a proper order. Libreoffice Calc has the tools to do this manually, but if the document has ongoing changes, that manual effort becomes extraordinarily time consuming and tedious. ----------- The rest of this post will detail real world limitations ----------- So my current example, which could be solved by these functions being implemented, goes as follows: I have a budget/finance system which has separate sheets as ledgers for income, expenses, transfers between accounts. There are then other sheets which extrapolate as much data as possible from those ledgers allowing you to see things like totals from different income types, various expenses broken down, averages per month, detailed account information, stock information, and on and and and on. (This requires some not as wacky array workarounds, but it works.) Now stocks are where some wacky array workarounds start. If I want to pluck the purchase of a stock out of the Expenses Sheet (and all related information), I need a named range/formula expression called, "ExpensesToStock" with: IF(ExpensesType="Stock">0,(ExpensesType="Stock")*(ROW(ExpensesType)),"") Where "ExpensesType" is the column titled Type in the Expenses Sheet. "ExpensesToStock" is now an array of row numbers where that data is in the Expenses Sheet, and blank spaces where the data isn't. Then I remove the blank spaces on a separate configuration sheet by grabbing the "Small" of those row numbers out of "ExpensesToStock" by putting this function in consecutive rows: {=IFERROR(SMALL(ExpensesToStock,ROW($A1)),"")} Each consecutive row then increments as a variable for the small. (ROW($A1),ROW($A2),...) Now that I have a list of rows where the data is, I can run an "Indirect" function to display all of the relevant data on a separate sheet called "Stocks." That's a filtered ledger of only stocks that I can then do additional calculations on to provide even more stock relevant data. ^ TL;DR, Functions automatically copy each Stock line out of the "Expenses" sheet into the "Stocks" sheet and calculate additional important information related to the Stocks. However, here is where the limitations of Calc array handling arise. Stocks aren't just an expense. Stocks can be an expense, income, or a merging between companies. If I wanted to make a dynamic ledger to determine proper data (total stocks, total basis, etc.) I need to evaluate both the purchase, sale, and transfer from the income and expenses sheet for all stock related information. You can imagine that the stock purchase and sale date are pretty important for the order of calculating. Combining both the purchase and sale of stocks plucked from 2 different sheets into a single date sorted ledger would be trivial and automatic with a filter and sort array function, but currently impossible in Calc short of constant manual efforts.
Please don't change the Summary. There is a different bug 127923 for XLOOKUP implementation
(In reply to Roman Kuznetsov from comment #10) > Please don't change the Summary. There is a different bug 127293 for XLOOKUP > implementation
*** Bug 147224 has been marked as a duplicate of this bug. ***
I was about to submit a bug report requesting for these functions, but fortunately I found this first. These functions are super useful and I've seen a handful of questions about whether LO Calc has them. +1 for this enhancement request.
Just a little reminder that these functions are still missing :( and people who want to use them are forced to use either Microsoft 365, Office 2021 or Google Sheets.
(Hoping this is not just noise, but can help somebody) I know it's not a complete solution, but in a hurry, this extension: https://github.com/goosepirate/lox365 can be useful.
*** Bug 156698 has been marked as a duplicate of this bug. ***
I completely agree! Google Sheets is getting too powerful and I need to use it more often because Libre calc is missing functions like FILTER and SORT which are essential for my spreadsheets. I hope libre calc devs code those much needed enhancements...
(In reply to Jacob H from comment #9) > I'll start by saying that dynamic Array manipulation is critical in handling > data and has so many use cases. In my opinion the dynamic handling of arrays > is the only remaining missing link in both function and form, at least as > far as data handling in Calc. Dynamic arrays https://support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-89a1-87a7eb36e531
*** Bug 159105 has been marked as a duplicate of this bug. ***
Balazs Varga committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/b0791dd9216224bdb266fd0d8c87df253b6d0583 tdf#126573 Add Excel2021 array function FILTER to Calc It will be available in 24.8.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Balazs Varga committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/e5361d0ef04bc63e7809323750aabe6efafcaef9 Related: tdf#126573 Add Excel2021 array function FILTER to Calc It will be available in 24.8.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Balazs Varga committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/45435a680be065e44eba385bb2523b27b77fb451 tdf#126573 Add Excel2021 array function SORT to Calc It will be available in 24.8.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Balazs Varga committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/bfb4c58ae708c75949559290bdfdd9afcef6aa91 tdf#126573 Add Excel2021 array function SORTBY to Calc It will be available in 24.8.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Balazs Varga committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/be938f2fb1c0a18658b4170396a22c64b77bf646 Related tdf#126573 Add Excel2021 array function SORT to Calc It will be available in 24.8.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Balazs Varga committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/35772a003bb30be61f8ba8abe805455e41db0e1e tdf#126573 Add Excel2021 array function SEQUENCE to Calc It will be available in 24.8.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.