## Extract a list of alphabetically sorted duplicates based on a condition

The following image shows you a data set in column B and C. The formula in cell E2 extracts a list of duplicates sorted from A to Z based on a condition in cell F2.

"Fishing rod" is a duplicate value in column C but it is not in category "Winter", however "Skates" and "Sledge" are duplicates and in category "Winter.

**Array formula in cell E5:**

The following article shows you how to filter unique distinct values sorted alphabetically and based on a condition:

**Unique distinct list sorted alphabetically and based on a condition**

Comments(1) Filed in category: Excel, Sorted unique distinct values, VLOOKUP and return multiple values

### How to enter array formula

- Copy (Ctrl + c) above formula
- Double click on cell E5
- Paste formula to cell E5
- 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.

**Learn the basics of Excel arrays**

Comments(2) Filed in category: Built-in features, Count values, Excel

### Explaining formula in cell E5

You can easily follow along, select cell E5. Go to tab "Formulas" on the ribbon and click "Evaluate Formula" button.

Then click "Evaluate" button to move to next step.

**Step 1 - Check if category is right and return count**

**Step 1 - Check if category is right and return count**

COUNTIFS($B$3:$B$9, $F$2, $C$3:$C$9, $C$3:$C$9)>1

becomes

COUNTIFS($B$3:$B$9, $F$2, $C$3:$C$9, $C$3:$C$9)>1

becomes

COUNTIFS({"Winter"; "Winter"; "Winter"; "Summer"; "Winter"; "Summer"; "Winter"},"Winter",{"Skates"; "Ski"; "Skates"; "Fishing rod"; "Sledge"; "Fishing rod"; "Sledge"},{"Skates"; "Ski"; "Skates"; "Fishing rod"; "Sledge"; "Fishing rod"; "Sledge"})>1

and returns

{2;1;2;0;2;0;2}

Comments(1) Filed in category: Excel, Functions

**Step 2 - Check if count is larger than 1 **

**Step 2 - Check if count is larger than 1**

This allows us to filter duplicate values.

COUNTIFS($B$3:$B$9, $F$2, $C$3:$C$9, $C$3:$C$9)>1

becomes

{2;1;2;0;2;0;2}>1

and returns {TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE}

**Step 3 - Count previous values in list**

**Step 3 - Count previous values in list**

COUNTIF($E$4:E4, $C$3:$C$9)

returns {0;0;0;0;0;0;0}

Comments(5) Filed in category: Excel, Functions

**Step 4 - Check if they have not been shown before**

**Step 4 - Check if they have not been shown before**

This makes sure that previous values in column E are not repeated.

COUNTIF($E$4:E4, $C$3:$C$9)>0

becomes

{0;0;0;0;0;0;0}=0

and returns {TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}

**Step 5 - Multiply arrays**

**Step 5 - Multiply arrays**

(COUNTIFS($B$3:$B$9, $F$2, $C$3:$C$9, $C$3:$C$9)>1)*(COUNTIF($E$4:E4, $C$3:$C$9)=0)

becomes

{TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE}*{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}

and returns {1;0;1;0;1;0;1}

**Step 6 - Build an array of sort ranking numbers**

**Step 6 - Build an array of sort ranking numbers**

COUNTIF($C$3:$C$9, "<"&$C$3:$C$9) returns {2;4;2;0;5;0;5}

**Step 7 - Return corresponding sort rank number**

**Step 7 - Return corresponding sort rank number**

IF((COUNTIFS($B$3:$B$9, $F$2, $C$3:$C$9, $C$3:$C$9)>1)*(COUNTIF($E$4:E4, $C$3:$C$9)=0), COUNTIF($C$3:$C$9, "<"&$C$3:$C$9), "")

becomes

IF({1;0;1;0;1;0;1},{2;4;2;0;5;0;5}, "")

and returns {2;"";2;"";5;"";5}

Comments(9) Filed in category: Excel, Functions

**Step 8 - Find the n-th smallest number**

**Step 8 - Find the n-th smallest number**

SMALL(IF((COUNTIFS($B$3:$B$9, $F$2, $C$3:$C$9, $C$3:$C$9)>1)*(COUNTIF($E$4:E4, $C$3:$C$9)=0), COUNTIF($C$3:$C$9, "<"&$C$3:$C$9), ""), ROWS($A$1:A1))

becomes

SMALL({2;"";2;"";5;"";5}, ROWS($A$1:A1))

becomes

SMALL({2;"";2;"";5;"";5}, 1)

and returns 2.

**SMALL function and LARGE function**

Comments(12) Filed in category: Excel, Functions

**Step 9 - Get position of value in array**

**Step 9 - Get position of value in array**

MATCH(SMALL(IF((COUNTIFS($B$3:$B$9, $F$2, $C$3:$C$9, $C$3:$C$9)>1)*(COUNTIF($E$4:E4, $C$3:$C$9)=0), COUNTIF($C$3:$C$9, "<"&$C$3:$C$9), ""), ROWS($A$1:A1)), COUNTIF($C$3:$C$9, "<"&$C$3:$C$9), 0)

becomes

MATCH(2, COUNTIF($C$3:$C$9, "<"&$C$3:$C$9), 0)

becomes

MATCH(2, {2;4;2;0;5;0;5}, 0)

and returns 1.

Comments(12) Filed in category: Excel, Functions

**Step 10 - Return value in data set based on coordinate**

**Step 10 - Return value in data set based on coordinate**

INDEX($C$3:$C$9, MATCH(SMALL(IF((COUNTIFS($B$3:$B$9, $F$2, $C$3:$C$9, $C$3:$C$9)>1)*(COUNTIF($E$4:E4, $C$3:$C$9)=0), COUNTIF($C$3:$C$9, "<"&$C$3:$C$9), ""), ROWS($A$1:A1)), COUNTIF($C$3:$C$9, "<"&$C$3:$C$9), 0))

becomes

INDEX($C$3:$C$9, 1)

becomes

INDEX({"Skates";"Ski";"Skates";"Fishing rod";"Sledge";"Fishing rod";"Sledge"}, 5)

and returns "Skates" in cell E5.

Comments(14) Filed in category: Excel, Functions, Index

### Download excel *.xlsx file

Filter duplicates sorted from A to Z based on a condition.xlsx

### Category: Duplicate values

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 […]Comments(41) Filed in category: Duplicate values, Excel

Extract a list of duplicates from three columns combined using array formula in excel

Question: I have three ranges or lists and I need to extract duplicates from those ranges combined using excel array […]Comments(7) Filed in category: Duplicate values, Excel

Filter unique distinct and duplicate values from a large data set in excel 2007

In this post I am going to describe how to filter duplicate and unique distinct values from a really large […]Comments(4) Filed in category: Case sensitive, Duplicate values, Excel, Unique distinct values

Find min and max unique and duplicate numerical values

Question: How do I get the largest and smallest unique and duplicate value? The image below shows you a list […]Comments(4) Filed in category: Duplicate values, Excel, Unique values

How to extract a list of duplicates from a column in excel

Edit: A better shorter easier array formula in this post. Here is a solution (Column C) to extract duplicates from […]Comments(4) Filed in category: Duplicate values, Excel

Remove duplicates on same date in excel

Question: Column A1 Has dates Column B as data A1 : 1/1/2010 : 5000 A2 : 2/1/2010 : 4000 A3 […]Comments(3) Filed in category: Dates, Duplicate values, Excel, Unique distinct values

Excel udf: Remove duplicates from a large dataset

Aamer asks: I have a sheet with 3000 rows of invoice dates that are out of order. There could be […]Comments(3) Filed in category: Duplicate values, Excel, Unique distinct values, User defined functions (udf), VBA

Extract duplicate values with exceptions

The formula in cell E2 returns duplicate values from column A but values in column C are excluded from the […]Comments(3) Filed in category: Duplicate values, Excel

Extract duplicates and their adjacent values

Question: How do i only put duplicate cell values from a list into another new list and their adjacent values? […]Comments(3) Filed in category: Duplicate values, Excel

Extract a list of duplicates from two columns combined using array formula in excel

Question: I have two ranges or lists where I want to extract duplicates? Answer: Excel 2007 formula in D2: =IFERROR(IFERROR(INDEX(List1, […]Comments(2) Filed in category: Duplicate values, Excel

### One Response to “Extract a list of alphabetically sorted duplicates based on a condition”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

[…] https://www.get-digital-help.com/2017/08/24/extract-a-list-of-alphabetically-sorted-duplicates-based… […]