https://bugs.documentfoundation.org/show_bug.cgi?id=126573
--- Comment #9 from [email protected] --- 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. -- You are receiving this mail because: You are the assignee for the bug.
