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

extract-a-list-of-duplicates-from-a-column

Update 2017-08-19! New regular formula in cell C2:

=LOOKUP(2, 1/(((COUNTIF($E$1:E1, $A$2:$A$20)=0)*(COUNTIF($A$2:$A$20, $A$2:$A$20)>1))), $A$2:$A$20)

This video explains how to use the formula and how it works

If you want a list sorted from A to Z, read this article:

Extract a list of alphabetically sorted duplicates from a column

Question: How do I create a new list of alphabetically sorted duplicates using excel array formula? Answer: Excel array formula […]

Comments(1) Filed in category: Duplicate values, Excel, Sort values

This post explains how to extract duplicate values using a condition:

Create a list of duplicates where adjacent cell value meets a condition

Question: How do I filter duplicates with a condition? Answer: Column B contains category and column C contains Items. Only […]

Comments(0) Filed in category: Duplicate values, Excel

You can also use more than one condition, this article shows you how:

Filter duplicate values using critera

The following worksheet allows you to search for duplicate names using a date range (cell B1 and B2) and a […]

Comments(0) Filed in category: Duplicate values, Excel

Filter a unique distinct list:

5 easy ways to extract unique distinct values

Update: 30 Aug, 2017h You have quite a few options to choose from if you are looking for a way […]

Comments(149) Filed in category: Advanced filter, Excel, Unique distinct values, Unique values

Array formula in C2:

=INDEX($A$2:$A$20, MATCH(0, COUNTIF(C1:$C$1, $A$2:$A$20)+IF(COUNTIF($A$2:$A$20, $A$2:$A$20)>1, 0, 1), 0))

How to enter an array formula

  1. Copy (Ctrl + c) above formula
  2. Double click on cell C2
  3. Paste (Ctrl + v) to cell C2
  4. Press and hold CTRL + SHIFT simulatneously
  5. Press Enter once
  6. Release all keys

Your formula now looks like this: {=array_formula}
Don't enter the curly brackets, they appear automatically.

Learn the basics of Excel arrays

Array formulas allows you to do advanced calculations not possible with regular formulas.

Comments(2) Filed in category: Count values, Excel

How to copy formula

Copy cell C2 and paste it to cells below as far as needed.

Remove #num errors:

=IFERROR(INDEX($A$2:$A$20, MATCH(0, COUNTIF(C1:$C$1, $A$2:$A$20)+IF(COUNTIF($A$2:$A$20, $A$2:$A$20)>1, 0, 1), 0)), "")

Copy cell C2 and paste it down to D20.

Learn more:

IFERROR function

The IFERROR function was introduced in excel 2007. In previous excel versions you could check for errors with the ISERROR […]

Comments(0) Filed in category: Excel

Earlier Excel versions, array formula in C2:

=IF(ISERROR(INDEX($A$2:$A$20, MATCH(0, COUNTIF(C1:$C$1, $A$2:$A$20)+IF(COUNTIF($A$2:$A$20, $A$2:$A$20)>1, 0, 1), 0))), "", INDEX($A$2:$A$20, MATCH(0, COUNTIF(C1:$C$1, $A$2:$A$20)+IF(COUNTIF($A$2:$A$20, $A$2:$A$20)>1, 0, 1), 0)))

How this array formula works

Step 1 - Show a duplicate value only once

=INDEX($A$2:$A$20, MATCH(0, COUNTIF(C1:$C$1, $A$2:$A$20)+IF(COUNTIF($A$2:$A$20, $A$2:$A$20)>1, 0, 1), 0))

COUNTIF(C1:$C$1, $A$2:$A$20) contains both a relative and absolute reference (C1:$C$1) to a range.

When you copy a cell reference like this the cell reference expands.

COUNTIF(C1:$C$1, $A$2:$A$20) returns this array in cell C2: (0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)

COUNTIF function

Counts the number of times a value exists in a cell range.

Comments(5) Filed in category: Excel

Step 2 - Filter values in $A$2:$A$20 having duplicates

=INDEX($A$2:$A$20, MATCH(0, COUNTIF(C1:$C$1, $A$2:$A$20)+IF(COUNTIF($A$2:$A$20, $A$2:$A$20)>1, 0, 1), 0))

COUNTIF($A$2:$A$20, $A$2:$A$20) counts the number of cells within a range that meet the given condition. The returning array is (2, 1, 1, 2, 1, 1, 2, 2, 1, 2, 2, 1, 2, 1, 1, 2, 1, 1, 1, 1).

COUNTIF($A$2:$A$20, $A$2:$A$20)>1 returns this array: (TRUE, FALSE, FALSE, TRUE, FALSE, FALSE, TRUE, TRUE, FALSE, TRUE, TRUE, FALSE, TRUE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE).

IF(COUNTIF($A$2:$A$20, $A$2:$A$20)>1, 0, 1) returns (1, 0, 0, 1, 0, 0, 1, 1, 0, 1, 1, 0, 1, 0, 0, 1, 0, 0, 0, 0).

IF function explained

Check if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

Comments(9) Filed in category: Excel

Step 3 - Calculate arrays combined

=INDEX($A$2:$A$20, MATCH(0, COUNTIF(C1:$C$1, $A$2:$A$20)+IF(COUNTIF($A$2:$A$20, $A$2:$A$20)>1, 0, 1), 0))

COUNTIF(C1:$C$1, $A$2:$A$20)+IF(COUNTIF($A$2:$A$20, $A$2:$A$20)>1, 0, 1)

(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0) + (1, 0, 0, 1, 0, 0, 1, 1, 0, 1, 1, 0, 1, 0, 0, 1, 0, 0, 0, 0) equals

(1, 0, 0, 1, 0, 0, 1, 1, 0, 1, 1, 0, 1, 0, 0, 1, 0, 0, 0, 0)

Step 4 - Identify duplicates

=INDEX($A$2:$A$20, MATCH(0, COUNTIF(C1:$C$1, $A$2:$A$20)+IF(COUNTIF($A$2:$A$20, $A$2:$A$20)>1, 0, 1), 0))

MATCH(0, COUNTIF(C1:$C$1, $A$2:$A$20)+IF(COUNTIF($A$2:$A$20, $A$2:$A$20)>1, 0, 1), 0) becomes

MATCH(0, (1, 0, 0, 1, 0, 0, 1, 1, 0, 1, 1, 0, 1, 0, 0, 1, 0, 0, 0, 0), 0) is 2.

Match returns the relative position of an item in an array that matches a specified value

MATCH function

Identify the position of a value in an array.

Comments(12) Filed in category: Excel

Step 5 - Return duplicates

=INDEX($A$2:$A$20, MATCH(0, COUNTIF(C1:$C$1, $A$2:$A$20)+IF(COUNTIF($A$2:$A$20, $A$2:$A$20)>1, 0, 1), 0))

becomes

=INDEX($A$2:$A$20, 2) is Federer, Roger.

Index returns a value or reference of the cell at the intersection of a particular row and column, in a given range

INDEX function explained

Fetch a value in a data set based on coordinates.

Comments(14) Filed in category: Cross reference table, Excel, INDEX function

Final thoughts

When formula in c2 is copied to c3 the refence changes.

Example

The formula in c2: =INDEX($A$2:$A$20, MATCH(0, COUNTIF(C1:$C$1, $A$2:$A$20)+IF(COUNTIF($A$2:$A$20, $A$2:$A$20)>1, 0, 1), 0))

Then copy the formula to C3.

The formula references changes: =INDEX($A$2:$A$20, MATCH(0, COUNTIF(C2:$C$1, $A$2:$A$20)+IF(COUNTIF($A$2:$A$20, $A$2:$A$20)>1, 0, 1), 0))

Read more about absolute and relative cell references.

This makes it possible to avoid previous cell values (C2) and only calculate remaining values.

Download excel sample file for this tutorial.

how-to-extract-a-list of duplicates from a columns-in-excelv2.xlsx
(Excel 2007 Workbook *.xlsx)