Extract duplicates from a range using excel array formula

Filed in Excel on Jul.13, 2009.

Inspired by a comment in this post Unique values from multiple columns using array formulas, I have now created an array formula that extracts duplicates (repeated values) from a range spanning multiple columns and rows.

Excel 2007 array formula in A2:

Formula in A2:

=INDEX(tbl, MIN(IF((COUNTIF($A$1:A1, tbl)+IF(COUNTIF(tbl, tbl)>1, 0, 1))=0, ROW(tbl)-MIN(ROW(tbl))+1)), MATCH(0, IF(COUNTIF($A$1:A1, INDEX(tbl, MIN(IF((COUNTIF($A$1:A1, tbl)+IF(COUNTIF(tbl, tbl)>1, 0, 1))=0, ROW(tbl)-MIN(ROW(tbl))+1)), , 1))=0, 0, 1)+IF(COUNTIF(tbl, INDEX(tbl, MIN(IF((COUNTIF($A$1:A1, tbl)+IF(COUNTIF(tbl, tbl)>1, 0, 1))=0, ROW(tbl)-MIN(ROW(tbl))+1)), , 1))>1, 0, 1), 0), 1) + CTRL + SHIFT + ENTER copied down as far as necessary.

Remove any errors with IFERROR() function:

=IFERROR(INDEX(tbl, MIN(IF((COUNTIF($A$1:A1, tbl)+IF(COUNTIF(tbl, tbl)>1, 0, 1))=0, ROW(tbl)-MIN(ROW(tbl))+1)), MATCH(0, IF(COUNTIF($A$1:A1, INDEX(tbl, MIN(IF((COUNTIF($A$1:A1, tbl)+IF(COUNTIF(tbl, tbl)>1, 0, 1))=0, ROW(tbl)-MIN(ROW(tbl))+1)), , 1))=0, 0, 1)+IF(COUNTIF(tbl, INDEX(tbl, MIN(IF((COUNTIF($A$1:A1, tbl)+IF(COUNTIF(tbl, tbl)>1, 0, 1))=0, ROW(tbl)-MIN(ROW(tbl))+1)), , 1))>1, 0, 1), 0), 1),"") + CTRL + SHIFT + ENTER copied down as far as necessary.

Excel 2003 array formula in A2:

=INDEX(tbl, SMALL(IF((COUNTIF(tbl, tbl)>1)*(COUNTIF($A$1:A1, tbl)=0), ROW(tbl)-MIN(ROW(tbl))+1), ROW(1:1)), MATCH(0, COUNTIF($A$1:A1, INDEX(tbl, SMALL(IF((COUNTIF(tbl, tbl)>1)*(COUNTIF($A$1:A1, tbl)=0), ROW(tbl)-MIN(ROW(tbl))+1), ROW(1:1)), , 1))+(COUNTIF(tbl, INDEX(tbl, SMALL(IF((COUNTIF(tbl, tbl)>1)*(COUNTIF($A$1:A1, tbl)=0), ROW(tbl)-MIN(ROW(tbl))+1), ROW(1:1)), , 1))<2), 0))+ CTRL + SHIFT + ENTER copied down as far as necessary.

To extract numerical duplicate values, see this blog post: How to find duplicates in a range spanning multiple columns?

Named ranges

tbl (C2:F5)
What is named ranges?

How to implement array formula to your workbook

Change named ranges. If your list starts at, for example, cell F3. Change $A$1:A1 in the above formula to F2:$F$2.

Download excel 2007 sample file for this article

extract-duplicates-from-a-range.xlsx
(Excel 2007 Workbook *.xlsx)

Download excel 2003 sample file for this article

Extract duplicates from a range.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article

IF(logical_test,[value_if:true],[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

ROW(reference)
returns the row number of a reference

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value

This blog article is one out of six articles on the same subject.

Extract a list of duplicates from a column using array formula in excel
Extract a list of duplicates from two columns combined using array formula in excel
Extract a list of duplicates from three columns combined using array formula in excel
Extract a list of alphabetically sorted duplicates from a column in excel
Filter duplicates from two columns combined and sort from A to Z using array formula in excel
Extract duplicates from a range using excel 2007 array formula

Share and Enjoy: