## Extract duplicates from a range using excel array formula

### Overview

Inspired by a comment in this post Unique values from multiple columns using array formulas, I 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:

Copy cell A2 and paste it down as far as needed.

Remove any errors with IFERROR() function:

Copy cell A2 and paste it down as far as needed.

### Excel 2003 array formula in A2:

Copy cell A2 and paste it down as far as needed.

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

Extract a list of duplicates from a column

The array formula in cell C2 extracts duplicate values from column A. Only one duplicate of each value is displayed […]

Extract a list of duplicates from three columns combined using array formula in excel

Question: I have three ranges or lists and I need to extract duplicates from those ranges combined using excel array […]

Filter unique distinct and duplicate values from a large data set

In this post I am going to describe how to filter duplicate and unique distinct values from a really large […]

Find min and max unique and duplicate numerical values

Question: How do I get the largest and smallest unique and duplicate value? The image below shows you a list […]

How to extract a list of duplicates from a column in excel

Edit: A better shorter easier array formula in this post. Here is a solution (Column C) to extract duplicates from […]

Extract duplicate values with exceptions

The formula in cell E2 returns duplicate values from column A but values in column C are excluded from the […]

Filter duplicate words from a cell range [UDF]

AJ Serrano asks: I have a column where each rows contains different values and I wanted to obtain the duplicate […]

Filter duplicates from two columns combined and sort from A to Z using array formula in excel

This blog post describes how to extract duplicates sorted from A to Z from two different columns combined. Array formula […]

Filter duplicates within same date, week or month in excel

Filter duplicates on same date Array formula in F3: =INDEX($C$3:$C$11, SMALL(IF(MATCH($C$3:$C$11&$D$3:$D$11, $C$3:$C$11&$D$3:$D$11, 0)<>ROW($C$3:$C$11)-MIN(ROW($C$3:$C$11))+1, ROW($C$3:$C$11)-MIN(ROW($C$3:$C$11))+1, ""), ROW(A1))) + CTRL + SHIFT […]

Extract a list of duplicates from two columns combined

Question: I have two ranges or lists where I want to extract duplicates? Answer: Excel 2007 formula in D2: =IFERROR(IFERROR(INDEX(List1, […]

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form