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 […]
This webpage demonstrates formulas that merge three non-adjacent cell ranges. What's on this webpage Extract a list of duplicates from […]
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 […]
This article demonstrates ways to extract unique distinct sorted from A to Z ignoring blanks, and based on a condition. […]
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.
This is not working for me; I keep getting #NAME showing up where the duplicate list should be.