## [Solved] How do I find Duplicate values from a given range

### [Solved] How do I find Duplicate values from a given range

...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...
Attachments
Last edited by Chris_A on Thu Dec 06, 2018 3:47 pm, edited 1 time in total.
Open Office 4.1.1
Windows 10 Home Edition
Chris_A

Posts: 12
Joined: Wed Oct 07, 2015 5:51 pm

### Re: How do I find Duplicate values from a given range...

Too difficult. Spreadsheets can't derive tables from other tables.
Select A1:E5
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x

Villeroy
Volunteer

Posts: 26399
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: How do I find Duplicate values from a given range...

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 with
Code: Select all   Expand viewCollapse view
`{=TEXTJOIN(";";1;IF(COUNTIF(A1:E5;ROW(A1:A50))>1;ROW(A1:A50);""))}`
where 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.
On Windows 10: LibreOffice 6.1 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Let's create a powerful UFO: United Free Office!
Lupp from München

Lupp
Volunteer

Posts: 2343
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

### Re: How do I find Duplicate values from a given range...

Thanks guys - appreciate the help!
Open Office 4.1.1
Windows 10 Home Edition
Chris_A

Posts: 12
Joined: Wed Oct 07, 2015 5:51 pm

### Re: [Solved] How do I find Duplicate values from a given ran

One of the problems with array function is ... Just enter one more duplicate into the data range and see.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x

Villeroy
Volunteer

Posts: 26399
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: [Solved] How do I find Duplicate values from a given ran

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

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.

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.1 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Let's create a powerful UFO: United Free Office!
Lupp from München

Lupp
Volunteer

Posts: 2343
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany