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

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

The picture above shows how to merge two columns into one list using a formula. If you are looking […]

Combine data from multiple sheets

Question: Problem description (simplified of course): I have a list of employees (by ID number) and date (by yr & […]

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

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

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

Filter unique distinct and duplicate values from a large data set

In this post I am going to describe how to filter duplicate and unique distinct values from a really large […]

Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]

Sort dates within a date range

Question: I have a list of unsorted dates. I want to extract records between two dates and I want the […]

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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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