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 […]

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 […]

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 […]

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 […]

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.

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 […]

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)

Learn to use the COUNTIF function

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

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.

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

How to use Excel’s MATCH function

Identify the position of a value in an array.

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.

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)