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

Question: How do I create a new list of alphabetically sorted duplicates using excel array formula?

Answer:

Excel array formula in C2:

=INDEX(List1, MATCH(MIN(IF(COUNTIF(List1, List1)*IF(COUNTIF(C1:$C$1, List1)=1, 0, 1)>1, COUNTIF(List1, "<"&List1), "")), IF(COUNTIF(List1, List1)>1, COUNTIF(List1, "<"&List1), ""), 0)) + CTRL + SHIFT + ENTER copied down as far as necessary.

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-sorted-a-to-z-from-a-column.xls
(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

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

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

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 unique distinct list sorted alphabetically removing blanks from a range in excel
2. Unique distinct list from a column sorted A to Z using array formula in excel
3. Unique distinct list sorted based on occurrance in a column in excel
4. Filter a column and create a new unique list sorted from A to Z using array formula in excel
5. Filter unique distinct list sorted based on sum of adjacent values using array formula in excel
6. Create unique distinct list sorted based on text length using array formula in excel
7. Create a unique distinct sorted list containing both numbers text removing blanks in excel
8. Validate duplicates in excel