## 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 click "Evaluate Formula" button, then click "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)

### Download excel sample file for this tutorial

5 easy ways to extract unique distinct values

You have quite a few options to choose from if you are looking for a way to create a unique […]

Count cells between a given value

jeyner asks: I need to count in a list the interval between the same value. Example list, 1-2-3-1-4-5-1-6-7-8-9-7-8-1 So the […]

Filter unique values and sort based on adjacent date

The formula in cell F3 extracts unique values from column C, the formula in cell F3 extracts the corresponding dates […]

List all unique events in a month

Question: I have a table with four columns, Date, Name, Level, and outcome. The range is from row 3 to […]

Find min and max unique and duplicate numerical values

Question: How do I get the largest and smallest unique and duplicate value? The image below shows you a list […]

Filter unique values from a cell range

Unique values are values occurring only once in cell range. This is what I am going to demonstrate in this blog […]

Create numbers based on numerical ranges

The image above shows an array formula in cell B3 that calculates numbers based on the numerical ranges in cell […]

Filter unique words from a range [UDF]

This blog post describes how to create a list of unique words from a cell range. Unique words are all […]

Filter unique values sorted from A to Z

A unique value is a value that only exists once in a list. A unique distinct list contains all cell values […]

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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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/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.