Apache OpenOffice Community Forum

...And then list those values on a separate row or column. Here is an example with a screenshot:

Ex. I want to find all of the values in the range A1:B5 that occur more than once, and then list those values on a separate row. I do not want to list how many times a duplicate value occurs, but rather the duplicate value itself. As you can see from the example, the numbers 2 and 11 occur more than once...

Too difficult. Spreadsheets can't derive tables from other tables.
Select A1:E5
menu:Format>COnditional Format...
Condition1 : Formula Is : COUNTIF($A$1:$E$5; A1 )>1
Note: The relative address A1 refers to the currently active cell. Replace that with E5 if E5 is the currently active cell.
Add a new flashy cell style with red color or something and confirm the dialog.
Now every value that appears more than once is highlighted by the given cell style.

Always attach real data (here an .ods) instead of pictures with the probable exception that the question is explicitly about the view / rendering.

If you can assure that the numbers contained in the range are taken from a limited starting sequence of natural numbers, say from 1 through 50, you could do it in LibreOffice V5.4 or higher withwhere the curly brackets are not to type, but only show that the formula was entered for array-evaluation.
The result would be the sequence of numbers occurring more often than once returned as a text in one cell.
A corresponding function, it might be named TEXTSPLIT(), to get this sequence split into single numbers in as many cells a needed is not available also in LibO (or Excel). Since Basic has a Split-function it is easy to implement it with user code, however.
On the other hand user code can easily provide one function for everything you need independent of the mentioned assurance.

Thanks guys - appreciate the help!

One of the problems with array function is ... Just enter one more duplicate into the data range and see.

@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:(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 subexpressioneliminates 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.])