## Filter duplicates from two columns combined and sort from A to Z using array formula in excel

This blog post describes how to extract duplicates sorted from A to Z from two different columns combined.

### Array formula in D2:

copied down as far as necessary.

**Named ranges**

List1 (A2:A13)

List2 (B2:A14)

What is named ranges?

**How to implement array formula to your workbook**

Change named ranges. If your list starts at, for example, F2. Change $D$1:D1 in the above formula to F1:$F$1.

Download excel example file.

Filter duplicates from two columns and sort from A to Z.xls

(Excel 97-2003 Workbook *.xls)

**Functions in this article:**

**IF(**logical_test;[value_if:true];[value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**SMALL(**array,k**)** Returns the k-th smallest row number in this data set.

**NOT(**logical**)**

Changes FALSE to TRUE or TRUE to FALSE

**MATCH(**lookup_value;lookup_array; [match_type]

Returns the relative position of an item in an array that matches a specified value

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**COUNTIF(**range,criteria**)**

Counts the number of cells within a range that meet the given condition

**MIN(**number1,[number2]**)**

Returns the smallest number in a set of values. Ignores logical values and text

**MAX(**number1,[number2],**)
**Returns the largest value in a set of values. Ignores logical values and text.

*This blog article is one out of six articles on the same subject.*

Extract a list of duplicates from a column using array formula in excel

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

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

Extract a list of alphabetically sorted duplicates from a column in excel

Filter duplicates from two columns combined and sort from A to Z using array formula in excel

Extract duplicates from a range using excel 2007 array formula

### Category: Combinemerge

Merge two columns into one list in excel

Question: How do I merge two ranges into one list? Answer: Formula in C2: =IFERROR(INDEX($A$2:$A$6, ROWS(C1:$C$1)), IFERROR(INDEX($B$2:$B$3, ROWS(C1:$C$1)-ROWS($A$2:$A$6)), "")) Copy […]Comments(50) Filed in category: Combine/Merge, Excel

Combine data from multiple sheets in excel

Question: Problem description (simplified of course): I have a list of employees (by ID number) and date (by yr & […]Comments(29) Filed in category: Combine/Merge, Excel

Merge two columns with possible blank cells

Question: This article is terrific. Thanks so much for posting this solution! I do have one question: Let's say my […]Comments(28) Filed in category: Combine/Merge, Excel

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

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

### Category: Sort values

Comments(81) Filed in category: Excel, Sort values

Sort dates within a date range using excel array formula

Question: I have a list of unsorted dates. I want to extract records between two dates and I want the […]Comments(23) Filed in category: Excel, Sort values

Extract unique distinct values from a filtered table (udf and array formula)

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]Comments(17) Filed in category: Excel, Sort values, Unique distinct values

### 2 Responses to “Filter duplicates from two columns combined and sort from A to Z using array formula in excel”

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

This is an awesome resource.

I am struggling with a similar task but I need to REMOVE the duplicates from the two columns and get a "clean" (i.e. a list of unique names without the duplicates) as an output. It must have been discussed before, could you point me to a relevant article please?

Dimitri Kryukov,

Extract a unique distinct list from two columns using excel 2007 array formula