Inspired by a comment in this post I figured out a shorter easier array formula.

Excel 2007 array formula in C2:

=INDEX(List1, MATCH(0, COUNTIF(C1:$C$1, List1)+IF(COUNTIF(List1, List1)>1, 0, 1), 0)) + CTRL + SHIFT + ENTER. Copy cell C2 and paste it down as far as needed.

Remove #num errors:

=IFERROR(INDEX(List1, MATCH(0, COUNTIF(C1:$C$1, List1)+IF(COUNTIF(List1, List1)>1, 0, 1), 0)), "") + CTRL + SHIFT + ENTER copied down to D20.

Earlier Excel versions, array formula in C2:

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

How this array formula works

Step 1

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

COUNTIF(C1:$C$1, List1) 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.

End of example

COUNTIF(C1:$C$1, List1) 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)

Step 2

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

COUNTIF(List1, List1) 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(List1, List1)>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(List1, List1)>1, 0, 1) returns (1, 0, 0, 1, 0, 0, 1, 1, 0, 1, 1, 0, 1, 0, 0, 1, 0, 0, 0, 0).

Step 3

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

COUNTIF(C1:$C$1, List1)+IF(COUNTIF(List1, List1)>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

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

MATCH(0, COUNTIF(C1:$C$1, List1)+IF(COUNTIF(List1, List1)>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

Step 5

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

becomes

=INDEX(List1, 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

Final thoughts

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

Example

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

Then copy the formula to C3.

The formula references changes: =INDEX(List1, MATCH(0, COUNTIF(C2:$C$1, List1)+IF(COUNTIF(List1, List1)>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.

Named ranges

List1 (A2:A20)
What is named ranges?

How to implement array formula to your workbook

Change named ranges. If your duplicates list starts at, for example, F3. Change C1:$C$1 in the above formulas to F2:$F$2.

Download excel sample file for this tutorial.
how-to-extract-a-list-of-duplicates-from-a-column-in-excel.xlsx
(Excel 2007 Workbook *.xlsx)

Functions in this article:

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

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

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

IFERROR(value;value_if_error) Returns value_if_error if expression is an error and the value of the expression itself otherwise

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

Related posts:

1. Filter a column and create a new unique list sorted from A to Z using array formula in excel
2. Unique distinct list from a column sorted A to Z using array formula in excel
3. Extract unique values from a range using array formula in excel
4. Filter unique values from a range using array formula in excel
5. Extract numbers and text from a range using array formula in excel