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

Excel 2007 array formula in C2:

=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)))

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. Extract a list of duplicates from two columns combined using array formula in excel
2. Extract a list of duplicates from three columns combined using array formula in excel
3. Extract a list of alphabetically sorted duplicates from a column in excel
4. Extract duplicates from a range using excel array formula
5. Filter duplicates from two columns combined and sort from A to Z using array formula in excel
6. Extract a unique distinct list from two columns using excel 2007 array formula
7. Extract largest values from two columns using array formula in excel
8. Unique distinct list from a column sorted A to Z using array formula in excel
9. Extract a unique distinct list from three columns in excel
10. How to extract a list of duplicates of a column in excel