Extract a list of alphabetically sorted duplicates from a column

Author: Oscar Cronquist Article last updated on November 22, 2018

The following array formula extracts duplicate values sorted from A to Z from cell range B3:B21.

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

How to enter an array formula

Copy above formula
Double press with left mouse button on cell D3
Paste formula to cell D3
Press and hold CTRL + SHIFT simultaneously
Press Enter once
Release all keys

The formula now looks like this: {=arrayformula}

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

Explaining formula in cell C2

Step 1 - Count values

The COUNTIF fucntion counts values based on a condition or criteria, this way we can identify which values are duplicates and which are not.

COUNTIF($B$3:$B$21,$B$3:$B$21)

becomes

COUNTIF({"Federer, Roger "; "Djokovic, Novak "; "Murray, Andy "; ... ; "Almagro, Nicolas "},{"Federer, Roger "; "Djokovic, Novak "; "Murray, Andy "; ... ; "Almagro, Nicolas "})

and returns

{2;1;1;2;1;1;2;2;1;2;2;2;1;1;1;2;1;1;1}.

Step 2 - Display first instance of each duplicate

The following COUNTIF function makes sure that the output list only contains unique values. The first argument contains a cell reference that expands as the formula is copied to cells below.

COUNTIF(D2:$D$2,$B$3:$B$21)<>1

becomes

COUNTIF("Duplicates",{"Federer, Roger "; "Djokovic, Novak "; "Murray, Andy "; ... ; "Almagro, Nicolas "})<>1

becomes

{0;0;0;0;0;0;0;0; 0;0;0;0;0;0; 0;0;0;0;0}<>1

and returns

{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}.

Step 3 - Multiply arrays

Both values must return TRUE.

COUNTIF($B$3:$B$21,$B$3:$B$21)*(COUNTIF(D2:$D$2,$B$3:$B$21)

becomes

{2;1;1;2;1;1;2;2;1;2;2;2;1;1;1;2;1;1;1}*{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}

and returns

{2; 1; 1; 2; 1; 1; 2; 2; 1; 2; 2; 2; 1; 1; 1; 2; 1; 1; 1}.

Boolean	Boolean	Multiply
FALSE	FALSE	0
FALSE	TRUE	0
TRUE	TRUE	1

Step 4 - Replace TRUE with a number representing the order if list were sorted

The IF function returns a value based on a logical expression, if TRUE the second argument is returned, if FALSE the third argument.

IF(COUNTIF($B$3:$B$21,$B$3:$B$21)*(COUNTIF(D2:$D$2,$B$3:$B$21)<>1)>1,COUNTIF($B$3:$B$21,"<"&$B$3:$B$21),"")

becomes

IF({2; 1; 1; 2; 1; 1; 2; 2; 1; 2; 2; 2; 1; 1; 1; 2; 1; 1; 1}>1,COUNTIF($B$3:$B$21,"<"&$B$3:$B$21),"")

becomes

IF({TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE},COUNTIF($B$3:$B$21,"<"&$B$3:$B$21),"")

becomes

IF({TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE},{7;6;11;3;14;5;7;3;16;9;17;9;1;12;13;17;2;15;0},"")

and returns

{7;"";"";3;"";"";7;3;"";9;17;9;"";"";"";17;"";"";""}.

Step 5 - Get smallest value in array

The MIN function returns the samllest value in a cell range or array.

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), ""))

becomes

MIN({7;"";"";3;"";"";7;3;"";9;17;9;"";"";"";17;"";"";""})

and returns 3.

Step 6 - Replace TRUE in array with a number representing the order if list were sorted

IF(COUNTIF($B$3:$B$21,$B$3:$B$21)>1,COUNTIF($B$3:$B$21,"<"&$B$3:$B$21),"")

becomes

IF({TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE}, COUNTIF($B$3:$B$21,"<"&$B$3:$B$21),"")

becomes

IF({TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE}, {7;6;11;3;14;5;7;3;16;9;17;9;1;12;13;17;2;15;0},"")

and returns {7;"";"";3;"";"";7;3;"";9;17;9;"";"";"";17;"";"";""}.

Step 7 - Return relative position

MATCH(MIN(IF(COUNTIF($B$3:$B$21,$B$3:$B$21)*(COUNTIF(D2:$D$2,$B$3:$B$21)<>1)>1,COUNTIF($B$3:$B$21,"<"&$B$3:$B$21),"")),IF(COUNTIF($B$3:$B$21,$B$3:$B$21)>1,COUNTIF($B$3:$B$21,"<"&$B$3:$B$21),""),0)

becomes

MATCH(3, {7;"";"";3;"";"";7;3;"";9;17;9;"";"";"";17;"";"";""},0)

and returns 4.

Step 8 - Return value

The INDEX function returns a value from a cell range based on a row and column number. The cell range is a single column so the column number is not neccessary.

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

becomes

INDEX($A$2:$A$20, 4)

and returns "Davydenko, Nikolay " in cell D3.

Get excel sample file for this tutorial

how-to-extract-a-list-of-duplicates-sorted-a-to-z-from-a-column.xlsx

Duplicate values category

Extract a list of duplicates from a column
The array formula in cell C2 extracts duplicate values from column A. Only one duplicate of each value is displayed […]

Extract a list of duplicates from three columns combined
This webpage demonstrates formulas that merge three non-adjacent cell ranges. What's on this webpage Extract a list of duplicates from […]

Filter values that exists in all three columns
This article explains how to extract values that exist in three different columns, they must occur in each of the […]

See all articles in 'duplicate values' category

Sort values category

Sort a column alphabetically
Table of Contents Sort a column - Excel 365 Sort a column using array formula Two columns sorting by the […]

Extract a unique distinct list sorted from A to Z ignore blanks
This article demonstrates ways to extract unique distinct sorted from A to Z ignoring blanks, and based on a condition. […]

Sort dates within a date range
Array formula in D5: =SMALL(IF(($B$3:$B$12<=$E$3)*($B$3:$B$12>=$E$2), $B$3:$B$12, "A"), ROWS($A$1:A1)) How to create an array formula Copy array formula Select cell D5 […]

See all articles in 'sort values' category

Functions in this article

INDEX MATCH MIN IF COUNTIF

More than 1300 Excel formulas

Excel categories

Home page

Latest updated articles.

Excel Functions

More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.

Excel Formulas

More than 1300 formulas organized in subcategories.

Excel Tables

Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.

Advanced Filter

Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.

Data Validation

Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.

Drop Down List

Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.

Named Ranges

Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.

Excel Solver

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.

Charts

An Excel feature that lets you visualize data in a graph.

Conditonal Formatting

Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.

Pivot Tables

Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA

VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

Macros

A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.

UDFs

UDF stands for User Defined Functions and is custom built functions anyone can create.

One Response to “Extract a list of alphabetically sorted duplicates from a column”

Vanessa says:

October 24, 2012 at 3:54 pm

This is not working for me; I keep getting #NAME showing up where the duplicate list should be.

Reply

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form