Extract a unique distinct list from two columns using excel 2007 array formula
Question: I have two ranges or lists (List1 and List2) from where I would like to extract an unique distinct list? Merge two list without duplicates, in other words.
Answer:
Excel 2007
Formula in C2:
=IFERROR(IFERROR(INDEX(List1, MATCH(0, COUNTIF($C$1:C1, List1), 0)), INDEX(List2, MATCH(0, COUNTIF($C$1:C1, List2), 0))), "") + CTRL + SHIFT + ENTER copied down to C20.
Earlier versions of Excel
Formula in C2:
=IF(ISERROR(INDEX(List1, MATCH(0, COUNTIF($C$1:C1, List1), 0))), INDEX(List2, MATCH(0, COUNTIF($C$1:C1, List2), 0)), INDEX(List1, MATCH(0, COUNTIF($C$1:C1, List1), 0))) + CTRL + SHIFT + ENTER copied down to C20.
The ranges don´t have to be adjacent, these formulas work fine anyway.
Named ranges
List1 (A2:A20)
List2 (B2:B7)
What is named ranges?
How to customize the formula to your excel spreadsheet
Change named ranges. If your duplicates list starts at, for example, F3. Change C1:$C$1 in the above formulas to F2:$F$2.
Download excel sample file for this tutorial.
how-to-extract-a-unique-list-from-two-columns-in-excel-2007.xlsx
(Excel 2007 Workbook *.xlsx)
how-to-extract-a-unique-distinct-list-from-two-columns-in-excel-2003.xls
(Excel 97-2003 Workbook *.xls)
Functions in this article:
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
IFERROR(value;value_if_error) Returns value_if_error if expression is an error and the value of the expression itself otherwise
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:
October 26th, 2009 at 9:36 pm
Can you please post an Excel 2003 sample of this. The 2003 formula does not work.
Thanks
October 26th, 2009 at 9:57 pm
No problem Rav!
I have now attached an excel 2003 file to this blog post.
July 29th, 2010 at 9:50 pm
Hello - trying to use this formula on a spreadsheet (Excel 2007) very similar to the one you show in your screenshot. I'm getting no values returned, so tried to download your example file and instead of an .xlsx file it downloads a zip file that contains a bunch of .xml files. The 2003 example file downloads fine ...
Thanks for posting this!
July 29th, 2010 at 10:18 pm
OK, I manaaged to use the 2003 formula in my Excel 2007 and it works ... right down to cell C24. After that I get a "Value not available error". I see that your example is only 24 rows long, but I don't see anywhere in the formula that it specifies to stop at row 24. If I continue copy/pasting the formula in cells 25 and below, shouldn't it use relative formatting to adjust properly?
July 30th, 2010 at 10:33 pm
David,
I downloaded the .xlsx file and it works fine here.
I am not sure I understand but I´ll give it a try:
The relative reference should adjust properly. But if you add more values to any of the lists, the named ranges are not dynamic. You need to change the named ranges to include the new values.