Extract unique values 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 in column F.
The following formula returns values that are unique from two columns combined. Unique values are values that only exist once in both columns.
Formula in cell F3:
Explaining formula in cell F3
This formula may seem big, however, it is actually two smaller formulas combined. If you want to see the calculation steps then go to tab "Formulas" on the ribbon and press with left mouse button on "Evaluate Formula" button, then press with left mouse button on "Evaluate" button to move to next step in the calculation.
Step 1 - Prevent duplicate values
The COUNTIF function is really helpful in this situation, it counts the values in order to display unique values from two columns combined.
COUNTIF(F2:$F$2, $B$3:$B$6)
becomes
COUNTIF("Unique list", {"AA";"BB";"AA";"CC"})
and returns {0;0;0;0}.
Step - 2 - Count values in List1 against List 2
COUNTIF($D$3:$D$7, $B$3:$B$6)
becomes
COUNTIF({"CC";"DD";"DD";"CC";"EE"}, {"AA";"BB";"AA";"CC"})
and returns {0;0;0;2}. This tells us that only "CC" has a duplicate in List2.
Step 3 - Count values in List1
The following COUNTIF formula identifies unique values in cell range $B$3:$B$6
COUNTIF($B$3:$B$6, $B$3:$B$6)
becomes
COUNTIF({"AA";"BB";"AA";"CC"}, {"AA";"BB";"AA";"CC"})
and returns
{2;1;2;1}.
Step 4 - Add arrays
(COUNTIF(F2:$F$2, $B$3:$B$6)+COUNTIF($D$3:$D$7, $B$3:$B$6)+COUNTIF($B$3:$B$6, $B$3:$B$6))
becomes
{0;0;0;0} +Â {0;0;0;2} +Â {2;1;2;1}
equals
{2;1;2;3}.
Step 5 - Check if value in array is equal to 1
(COUNTIF(F2:$F$2, $B$3:$B$6)+COUNTIF($D$3:$D$7, $B$3:$B$6)+COUNTIF($B$3:$B$6, $B$3:$B$6))=1
becomes
{2;1;2;3}=1
and returns
{FALSE;TRUE;FALSE;FALSE}.
Step 6 - Divide 1 with array
1/((COUNTIF(F2:$F$2, $B$3:$B$6)+COUNTIF($D$3:$D$7, $B$3:$B$6)+COUNTIF($B$3:$B$6, $B$3:$B$6))=1)
becomes
1/({FALSE;TRUE;FALSE;FALSE})
and returns
{#DIV/0!;1;#DIV/0!;#DIV/0!}
Step 7 - Return value based on array
LOOKUP(2, 1/((COUNTIF(F2:$F$2, $B$3:$B$6)+COUNTIF($D$3:$D$7, $B$3:$B$6)+COUNTIF($B$3:$B$6, $B$3:$B$6))=1),$B$3:$B$6)
becomes
LOOKUP(2, {#DIV/0!;1;#DIV/0!;#DIV/0!},$B$3:$B$6)
becomes
LOOKUP(2, {#DIV/0!;1;#DIV/0!;#DIV/0!},{"AA";"BB";"AA";"CC"})
and returns "BB" in cell F3.
Step 8 - Return values from List 2
When the first formula returns errors the IFERROR function directs to the next formula. The next formula is exactly the same as the first formula except that it gets values from List 2.
=IFERROR(formula1, formula2)
Get excel sample file for this tutorial
unique list from two columnsv2.xlsx
Unique values category
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
The formula in cell F3 extracts unique values from column C, the formula in cell F3 extracts the corresponding dates […]
This article demonstrates a formula that extracts unique distinct records/rows for a given month specified in cell C2, see the […]
Excel categories
14 Responses to “Extract unique values from two columns”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
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.
https://www.get-digital-help.com/count-unique-distinct-values-in-three-columns-combined-in-excel/
Hi Oscar,
Would be possible to share this formula again.
It looks like link is years old and was probably disabled.
Much appreciate.
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?
Get the Excel 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.
How to just list out the unique from list 1
Nguyen Hai Tuan
Read this:
https://www.get-digital-help.com/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/
Can this formula be changed to an array formula? I also need the DISTINCT values (not UNIQUE values) from both the columns. for e.g. A,B,C from List 1 and C,D,E from List 2, which when combined should result in A,B,C,D,E.