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

Discuss the spreadsheet application

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

Postby Chris_A » Wed Dec 05, 2018 10:06 pm

...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
Chris_A
 
Posts: 12
Joined: Wed Oct 07, 2015 5:51 pm

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

Postby Villeroy » Thu Dec 06, 2018 12:01 am

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 25952
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby Lupp » Thu Dec 06, 2018 2:00 am

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
User avatar
Lupp
Volunteer
 
Posts: 2164
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

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

Postby Chris_A » Thu Dec 06, 2018 3:43 pm

Thanks guys - appreciate the help!
Chris Adams
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

Postby Villeroy » Thu Dec 06, 2018 4:00 pm

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
User avatar
Villeroy
Volunteer
 
Posts: 25952
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby Lupp » Fri Dec 07, 2018 12:35 pm

@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.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
User avatar
Lupp
Volunteer
 
Posts: 2164
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 30 guests