@Villeroy

Assuming your comment above is concerning the formula I suggested...

Answer:

The additional duplicate would be listed in the sequence.

Explanation:

That formula needs to be entered for array evaluation only to

ForceArray the third parameter.

The formula does not lock an output range, however. It delivers a semicolon-delimited sequence to a single cell.

Additional considerations:

A corresponding TEXTSPLIT() function is missing anyway (can't understand that). Such a function would need array-evaluation in any case, and output to a range. For private use I wrote such a function with some additional features.

It has an optional last parameter allowing for explicitly locking a minimum number of output cells. Example working for the request of this thread:

- Code: Select all Expand viewCollapse view
`=TRANSPOSE(XTEXTSPLIT(";";101;TEXTJOIN(";";1;IF(COUNTIF(A1:E5;XTEXTSPLIT(";";0;TEXTJOIN(";";1;A1:E5)))>1;XTEXTSPLIT(";";0;TEXTJOIN(";";1;A1:E5));""));50))`

(I did not yet include an option for sorting.) Here the final result suppresses repeated

listing of the same duplicate due to the second parameter of the final XTEXTSPLIT() (amount >=100). The inner subexpression

- Code: Select all Expand viewCollapse view
`XTEXTSPLIT(";";0;TEXTJOIN(";";1;A1:E5))`

eliminates the need to know the type (integer) and range (1..50) of the elements in advance.

(The function TEXTJOIN() is not explicitly specified in any place I would know of. It is supposed to do "as Excel 2016 does" seemingly. In my opinion the third and subsquent parameters should be specified

ForceArray, anyway. Trying a specification in the ODFF style, however, is problematic. The traditional accumulating numeric functions don't specify their respective parameters as arrays but as lists, and orders them to automatically convert arrays. I would hope this can be cleaned out one day. [There isn't yet a general specification for

List, but only for list types with (functionally) numeric elements.])

On Windows 10: LibreOffice 6.4 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2

---

Lupp from München