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
Related posts:
Comparing two columns and sum unique values using array formula 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
Recommended categories
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