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

*Article updated on December 07, 2010*

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

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

Remember to convert less than and larger than signs to html character entities before you post your comment.

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