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

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