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

Answer:

extract-a-list-of-alphabetically-sorted-duplicates-from-a-column

Excel array formula in C2:

=INDEX($A$2:$A$20, MATCH(MIN(IF(COUNTIF($A$2:$A$20, $A$2:$A$20)*IF(COUNTIF(C1:$C$1, $A$2:$A$20)=1, 0, 1)>1, COUNTIF($A$2:$A$20, "<"&$A$2:$A$20), "")), IF(COUNTIF($A$2:$A$20, $A$2:$A$20)>1, COUNTIF($A$2:$A$20, "<"&$A$2:$A$20), ""), 0))

This post explains how to extract duplicate values using a condition:

Create a list of duplicates where adjacent cell value meets a condition

Question: How do I filter duplicates with a condition? Answer: Column B contains category and column C contains Items. Only […]

Comments(0) Filed in category: Duplicate values, Excel

You can also use more than one condition to filter duplicate values, this article shows you how:

Filter duplicate values using critera

The following worksheet allows you to search for duplicate names using a date range (cell B1 and B2) and a […]

Comments(0) Filed in category: Duplicate values, Excel

The following article demonstrates how to sort a column using an array array formula:

Sort a column alphabetically

Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]

Comments(81) Filed in category: Excel, Sort values

How to filter a unique distinct list:

5 easy ways to extract unique distinct values

Update: 30 Aug, 2017h You have quite a few options to choose from if you are looking for a way […]

Comments(149) Filed in category: Advanced filter, Excel, Unique distinct values, Unique values

How to enter array formula

  1. Copy (Ctrl + c) above formula
  2. Double click on cell C2
  3. Paste formula to cell C2
  4. Press and hold CTRL + SHIFT simultaneously
  5. Press Enter once
  6. Release all keys

The formula now looks like this: {=arrayformula}

Don't enter the curly brackets yourself, they appear automatically.

How to copy formula

Copy cell C2 and paste it down as far as necessary.

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