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

The following image shows you a data set in columns 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.

### Table of Contents

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

Array formula in cell E5:

### 1.1 How to enter an array formula

- Copy (Ctrl + c) above formula
- Double press with left mouse button 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.

### 1.2 Explaining formula in cell E5

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

Then press with left mouse button on "Evaluate" button to move to next step.

#### Step 1 - Check if category matches condition

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}

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

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

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

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

(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

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

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

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

#### Step 9 - Get the positions of values in the 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.

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

## 2. Extract a list of alphabetically sorted duplicates based on a condition - Excel 365

Formula in cell F5:

### 2.1 Explaining formula in cell F5

#### Step 1 - Check condition

The equal sign checks if the condition is equal to values in cell range B3:B9.

B3:B9=F2

becomes

{"Winter";"Winter";"Winter";"Summer";"Winter";"Summer";"Winter"}="Winter"

and returns

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

#### Step 2 - Filter values based on criteria

The FILTER function filters values in a given cell range based on a condition or criteria.

FILTER(C3:C9, B3:B9=F2)

becomes

FILTER({"Skates"; "Ski"; "Skates"; "Fishing rod"; "Sledge"; "Fishing rod"; "Sledge"}, {TRUE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE})

and returns

{"Skates"; "Ski"; "Skates"; "Sledge"; "Sledge"}.

#### Step 3 - Assign a formula to a name

The LET function lets you assign a formula to a name. This reduces the formula size significantly.

FILTER(C3:C9, (B3:B9=F2) is assigned to z.

LET(z, FILTER(C3:C9, (B3:B9=F2)), formula)

#### Step 3 - Match filtered values

The MATCH function returns a number representing the relative position of an item in an array or cell range.

MATCH(z, z, 0)

becomes

MATCH({"Skates";"Ski";"Skates";"Sledge";"Sledge"}, {"Skates";"Ski";"Skates";"Sledge";"Sledge"}, 0)

and returns {1; 2; 1; 4; 4}.

#### Step 4 - Create a sequence based on the number of filtered values

The SEQUENCE function returns a sequence of numbers.

SEQUENCE(*rows*, [*columns*], [*start*], [*step*])

SEQUENCE(ROWS(z))

becomes

SEQUENCE(5)

and returns {1; 2; 3; 4; 5}.

#### Step 5 - Check for duplicates

The less than and greater than characters return TRUE if the numbers don't match indicating that the value is a duplicate.

MATCH(z, z, 0)<>SEQUENCE(ROWS(z))

becomes

{1; 2; 1; 4; 4}={1; 2; 3; 4; 5}

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

#### Step 6 - Filter values

FILTER(z, MATCH(z, z, 0)<>SEQUENCE(ROWS(z)))

becomes

FILTER({"Skates"; "Ski"; "Skates"; "Sledge"; "Sledge"}, {TRUE; TRUE; FALSE; TRUE; FALSE})

and returns {"Skates";"Sledge"}.

#### Step 6 - Sort array

The SORT function sorts values in a cell range or array from A to Z.

SORT(FILTER(z, MATCH(z, z, 0)<>SEQUENCE(ROWS(z))))

becomes

SORT({"Skates";"Sledge"})

and returns {"Skates";"Sledge"}.

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

The following array formula extracts duplicate values sorted from A to Z from cell range B3:B21. Excel array formula in […]

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

## Functions in this article

More than 1300 Excel formulas

## Excel formula categories

## Excel categories

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

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

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