## Create unique list from two columns

I read an article Merging Lists To A List Of Distinct Values at CPearson. The article describes code that you can use to merge two lists into a third list and prevent duplicate entries in the resulting list, using VBA to create a macro.

Here is a solution to create a unique list from two columns without using VBA.

The picture below shows the unique list and the unique distinct list.

**Named ranges**

*List1* (A2:A5)

*List2* (C2:C6)

**Unique values**

Here is the array formula in A10:

**Unique distinct values**

Here is the array formula in C10:

**How to create an array formula**

- Select cell C10
- Copy/Paste array formula
- Press and hold Ctrl + Shift
- Press Enter

**How to copy array formula in cell C10**

- Select cell C10
- Copy (Ctrl + c)
- Selelct cell range C11:C15
- Paste (Ctrl + v)

**How to customize the formula to your excel workbook**

Change the named ranges. If your unique list starts at F3, change C9:$C$9 (or A9:$A$9) in the above formula to $F$2:F2.

**Possible blanks in the two lists**

Array formula in c10:

**Download excel sample file for this tutorial.**

unique-list-from-two-columns.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

**ROW(**reference**)** returns the rownumber of a reference

**ROWS(**array**)** returns the number of rows in a reference or an array

**ADDRESS(**row_num,column_num**)**

creates a cell reference as text, given specified row and column numbers

**INDIRECT(**ref_text,[a1]**)**

Returns the reference specified by a text string

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

*This blog article is one out of thirteen articles on the same subject "unique".*

How to extract a unique distinct list from a column in excel

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

Extract a unique distinct list from three columns in excel

Extract distinct unique sorted year and month list from a date series in excel

Create a unique distinct list from a date range in excel

Unique values from multiple columns using array formulas

Extract a unique distinct list sorted from A-Z from range in excel

Sort a range by occurence using array formula in excel

Filter unique distinct values from two ranges combined in excel 2007

Create a unique list and sort by occurrances from large to small

Unique list to be created from a column where an adjacent column has text cell values

Create unique list from column where an adjacent column meets criteria

How to create a unique distinct list where other columns meet two criteria

### 10 Responses to “Create unique list from two columns”

### Leave a Reply

**How to add vba code to your comment:**

[vb 1="vbnet" language=","]

your code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**How to upload a file**

Upload file

Hi,

I've been searching for articles to help me to identify unique data across multiple Excel columns, and yours is the closest I have found. However, it seems to deal with only two (2) columns at a time. Is it expandable to cover more than two, in particular where the columns are not adjacent?

What I have is a grid of results:

Cols A, B, C reference result set 1 (with the potentially duplicate data in Col C)

Cols D, E, F reference result set 2 (with the potentially duplicate data in Col F)

and so on, with potentially result sets. The pracical number of rows involved is likely to be no more than 500.

What I need to do is to count all the unique entries in Col C, Col D, Col as a combined set, such that if Col C has data "abc", and Col also has the same data, then the data is only counted once. (I don't actually need to generate a new list of the unique data, but if that is a required stage in the calculation it is not a problem.) Note that can be any value, but is likely to not exceed 500 columns.

Could you advise me if there is a specific, formulaic (not VBA) solution to the problem? If so, could you provide a clue to solving it?

Many Thanks,

Nigel Edwards.

Thanks for commenting!

Interesting question, I have to think about this question for a while...

I created an array formula that counts unique distinct values in three different ranges.

http://www.get-digital-help.com/2009/06/11/count-unique-distinct-values-in-three-columns-combined-in-excel/

hi, i'm curious on this solution.

Is there a simpler/shorter formula to extract non-duplicate entries?

output shall be BB and EE (non-duplicates on both columns).

I have updated this blog post. I think the formula is somewhat shorter and uses less named ranges.

xcellent!!! thanks!!

I tried this and it gives a divide by zero error. The reason is countif() gives a 0. What are you trying to do with the countif, seems like that using 2 ranges are arguments is not allowed.

Scott,

I think you might have blanks in your two lists?

Download file

Scott.xls

Suppose if i want the unique list to be start from F3 means, then i have to change C9:$C$9 to F2:$F$2....but i want the list to be start from F1 then what should i write in the formula...instead of C9:$C$9 can i write F0:$F$0. Note: I dont want any heading. I want the list to be start from F1.Please provide the solution

Shariff,

Suppose if i want the unique list to be start from F3 means, then i have to change C9:$C$9 to F2:$F$2...Yes, correct!

but i want the list to be start from F1 then what should i write in the formula...instead of C9:$C$9 can i write F0:$F$0. Note: I dont want any heading. I want the list to be start from F1.Please provide the solution

Great question and I don´t know the answer. I don´t think you can.