Description: the newly introduced filter function in libreoffice 24.8 contains a bug where it incorrectly filters multiple data if the input apeears twice in the table but the output for each is different. Steps to Reproduce: 1.In cell B3 type in "Name" 2.In cell C3 type in "Number" 3.In cell B4 and B5 type in "Jerry" 4. In cell B6 type in John 5. In cell B7 type in "Larry" 6. In cell B8 type in Sam 7. From cells C4:C8 type in numbers 1-5 8. In cell E3 right click and select data validation, select cell range for validity. 9.) select B4:B8 10. In cell F3, enter the following function: =filter(C4:C8,B4:B8=E3," ") 11. then press ctrl+shift+enter to enter in the array based formula 12. If you select the names John,Larry or Sam from the data validation list the filter function is giving individual results, however if you select Jerry you'll get only the first number that is in the table. 13. If you drag the filter function formula down you'll now have 2 cells with the formula(extending the array), Now if you select Jerry you'll get both the desired vales of 1 and 2 from the table, but for any of the other names your getting the same numbers twice coming from the filter function. Actual Results: The filter function is only has one array set, will give one output for every 1 input, and if the filter function array is dragged down to extend the array(such as if the data table contains the same input appearing twice)then the filter function is able to give both different outputs for that 1 input; however for any 1 input(from the data table) where the function should be giving 1 output the filter function tend to produce the same output twice. Expected Results: The filter function producing the exact outputs shown in the table as the input entered, and not repeating output if it only shows in the table once. Reproducible: Always User Profile Reset: Yes Additional Info: This filter function exsists in Excel and google sheets and over there it tends to automatically detect from the table how many times an input appears in the data table, and if that same input appears more then once and has a different output the filter ensures the show it.
The feature "dynamic array" does not exist in LibreOffice. The feature request for it is in bug 127808. The fact that a one-row result is copied to subsequent rows of an existing larger output area is conform to ODF. That is item 2.1.3 in section 3.3 Non-Scalar Evaluation in https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/OpenDocument-v1.3-os-part4-formula.html#__RefHeading__1017866_715980110 This copying is only for a 1-row input into an output area with more than one rows. For a 2-row input into a e.g. 5-row output area, you would get #N/A error messages. I don't know why this rule was introduced in ODF. I personally want to replace the rule with another one, that allows applications to adapt the output area to the actual input instead. Discussion about that has just started in the ODF TC. And a possible solution depends how "dynamic array" will be implemented in LibreOffice and will be standardized in ODF. A quick solution is not to be expected. In the meantime you could workaround the problem. You can use the functions ROWS and COLUMNS to determine the actual size of the output of the FILTER function and with this information adapt the output area manually. Or you use the Advanced Filter of a Database Range. That adapts its output size to the actual size of the filter result. In case you need help on that, please use our ASK site. *** This bug has been marked as a duplicate of bug 127808 ***