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

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

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:

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

=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 all rows from a range that meet criteria in one column in excel
2. Count unique and duplicates text values in a closed workbook in excel (formula)
3. Filter duplicates within same date, week or month in excel
4. Filter a column and create a new unique list sorted from A to Z using array formula in excel
5. Create a list of all numbers or text in a column in excel