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.
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.
(Excel 97-2003 Workbook *.xls)
Functions in this article:
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
creates a cell reference as text, given specified row and column numbers
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".
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