## Extract a list of alphabetically sorted duplicates from a column

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

**Excel array formula in C2:**

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

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

The following regular formula extracts duplicate values from column B (List1), D (List2) and F (List3) combined, the result is […]

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

Table of Contents How to find the largest duplicate number How to find the largest duplicate number - Excel 365 […]

The image above demonstrates a formula in cell E3 that extracts duplicate items if they are on the same date. […]

The following regular formula extracts duplicates from column B (List1) and column D (List2) combined, the result is shown in […]

Michael asks: I need to identify the duplicates based on the Columns D:H and put in Column C a small […]

This article demonstrates formulas that extract duplicate values from a column, however, column D contains values that you don't want […]

Array formula in D2: =INDEX($A$2:$A$21, MATCH(SMALL(IF(COUNTIF($B$2:$B$21, $B$2:$B$21)>1, COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), ""),ROWS($A$1:A1)), COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), 0)) Array formula in E2: =INDEX($B$2:$B$21, MATCH(SMALL(IF(COUNTIF($B$2:$B$21, $B$2:$B$21)>1, […]

The following image shows you a data set in columns B and C. The formula in cell E2 extracts a […]

The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they contain string specified in cell […]

The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they begin with the condition specified […]

This article describes two formulas that extract duplicates from a multi-column cell range, the first one is built for Excel […]

This article demonstrates formulas and Excel tools that extract duplicates based on three conditions. The first and second condition is […]

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

### Sort values category

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

The image above demonstrates a formula in cell D3 that extracts unique distinct numbers and text values sorted from A […]

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 […]

This article demonstrates a formula that extracts values based on a condition and sorts the returned values based on values […]

The image above shows a table with two columns in cell range B3:C16, it contains random text values in column […]

This article demonstrates a formula that sorts cell values by their frequency, in other words, how many times a value […]

Table of Contents Sort text from two columns combined (array formula) How to create an array formula How to copy […]

This article demonstrates a macro and a formula that allows you to sort delimited data in a cell or cell […]

This article demonstrates a formula that sorts items arranged horizontally based on the adjacent numbers, every other column contains a […]

This article demonstrates how to sort a specific column in an Excel defined Table based on event code. The event […]

Question: How do I sort a range alphabetically using excel array formula? Answer: Cell range $B$2:$E$5 contains text values in random […]

The formula in cell B8 extracts a list sorted based on frequency. Array formula in B8: =TEXTJOIN("", TRUE, IF(MIN(IF((MAX(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, […]

Ralee asks in in this blog post: Sort values in parallel (array formula) If there is information in adjacent columns, […]

Overview The array formula in cell range C2:C6 creates a random list from the values in cell range A2:A6. Array […]

In this article, I will demonstrate two techniques for counting per row. The first example is simple and straightforward. The […]

The image above demonstrates a formula in cell D3 that sorts values based on character length, the value with the […]

I will in this article demonstrate three different techniques to sort a data set in Excel. The first method sorts […]

This article describes a formula that sorts values arranged in a column from A to z by every other value. […]

This article demonstrates how to distribute values into specific ranges with possible overlapping ranges. I have written articles about filter […]

## Functions in this article

More than 1300 Excel formulas

## Excel formula categories

## Excel categories

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

### Leave a 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

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