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

Discuss the spreadsheet application
Post Reply
Chris_A
Posts: 12
Joined: Wed Oct 07, 2015 5:51 pm

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

Post by Chris_A »

...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
Table.jpg
Last edited by Chris_A on Thu Dec 06, 2018 3:47 pm, edited 1 time in total.
Chris Adams
Open Office 4.1.1
Windows 10 Home Edition
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post by Villeroy »

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

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

Post by Lupp »

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

{=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 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Chris_A
Posts: 12
Joined: Wed Oct 07, 2015 5:51 pm

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

Post by Chris_A »

Thanks guys - appreciate the help!
Chris Adams
Open Office 4.1.1
Windows 10 Home Edition
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

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

Post by Lupp »

@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.])
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Post Reply