Edit: A better shorter easier array formula in this post.

Here is a solution (Column C) to extract duplicates from column A. No need for "helper"columns this time either.

Here is the formula in C2:C25: =IF(ROW()-1>SUM(IF(MATCH(A2:A20, A2:A20, 0)<ROW(A2:A20)-(MIN(ROW(A2:A20)-1)), 1, 0)), "", INDEX($A$2:$A$20, SMALL(IF(MATCH(A2:A20, A2:A20, 0)<ROW(A2:A20)-(MIN(ROW(A2:A20)-1)), ROW(A2:A20)-1, ""), ROW(A2:A20)-1))) + Ctrl + Shift + Enter

How to customize the formula to your excel spreadsheet

I have bolded the parts of the formula you need to edit:

=IF(ROW()-1>SUM(IF(MATCH(A2:A20, A2:A20, 0)<ROW(A2:A20)-(MIN(ROW(A2:A20)-1)), 1, 0)), "", INDEX($A$2:$A$20, SMALL(IF(MATCH(A2:A20, A2:A20, 0)<ROW(A2:A20)-(MIN(ROW(A2:A20)-1)), ROW(A2:A20)-1, ""), ROW(A2:A20)-1))) + Ctrl + Shift + Enter

If your range is C100:C500 the formula would be:

=IF(ROW()-1>SUM(IF(MATCH(C100:C500, C100:C500, 0)<ROW(C100:C500)-(MIN(ROW(C100:C500)-1)), 1, 0)), "", INDEX($C$100:$C$500, SMALL(IF(MATCH(C100:C500, C100:C500, 0)<ROW(C100:C500)-(MIN(ROW(C100:C500)-1)), ROW(C100:C500)-1, ""), ROW(C100:C500)-1))) + Ctrl + Shift + Enter

Download excel sample file for this tutorial.
how-to-extract-a-unique-list-and-the-duplicates-in-excel
(Excel 97-2003 Workbook *.xls)

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

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

INDIRECT(ref_text,[a1])
Returns the reference specified by a text string

ROW(reference) returns the rownumber of a reference

SUM(number1,[number2],)
Adds all the numbers in a range of cells

LARGE(array,k) returns the k-th largest row number in this data set.

Related posts:

1. Extract a list of duplicates from a column using array formula in excel
2. Extract a list of alphabetically sorted duplicates from a column in excel
3. Extract a list of duplicates from two columns combined using array formula in excel
4. How to extract a unique distinct list of a column in excel
5. Extract a list of duplicates from three columns combined using array formula in excel
6. Extract duplicates from a range using excel array formula
7. Create a list of duplicates where adjacent cell value meets a condition in excel
8. Extract all rows from a range that meet criteria in one column in excel
9. Create unique distinct list from column where an adjacent column meets criteria
10. Unique list to be created from a column where an adjacent column has text cell values