@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
=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
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.])