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

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.