## Filter values occurring in range 1 but not in range 2

The formulas above extracts values that exists only in one or the other cell range, if you are looking for a formula that compares two separate columns the go here: Compare two columns and show differences

Array formula in B13:

Array formula in B22:

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

If you don't own an Excel version that has the TEXTJOIN function then use the following considerably larger formula.

Array formula in B13:

copied down as far as necessary.

Formula in B21:

copied down as far as necessary.

### Explaining formula in cell B13

#### Step 1 - Identify values not shared by the ranges

The COUNTIF function counts values based on a condition or criteria, in this case, it counts values between the cell ranges.

COUNTIF($B$7:$E$10, $B$2:$D$4)=0

becomes

COUNTIF({"HH", "II", "JJ", "KK";"AA", "MM", "NN", "OO";"AA", "QQ", "RR", "SS";"TT", "II", "VV", "CC"}, {"AA", "BB", "CC";"DD", "BB", "EE";"FF", "CC", "GG"})=0

becomes

{2,0,1;0,0,0;0,1,0}=0

and returns

{FALSE, TRUE, FALSE;TRUE, TRUE, TRUE;TRUE, FALSE, TRUE}.

#### Step 2 - Prevent duplicates in list

COUNTIF(B12:$B$12, $B$2:$D$4)

becomes

COUNTIF("Values existing in range One but not in range Two", {"AA", "BB", "CC";"DD", "BB", "EE";"FF", "CC", "GG"})

and returns

{0,0,0;0,0,0;0,0,0}.

#### Step 3 - Add arrays

Check if array is equal to 1.

((COUNTIF($B$7:$E$10, $B$2:$D$4)=0)+COUNTIF(B12:$B$12, $B$2:$D$4))=1

becomes

({FALSE, TRUE, FALSE;TRUE, TRUE, TRUE;TRUE, FALSE, TRUE}+{0,0,0;0,0,0;0,0,0})=1

becomes

{0,1,0;1,1,1;1,0,1}=1

and returns

{FALSE,TRUE,FALSE;TRUE,TRUE,TRUE;TRUE,FALSE,TRUE}.

#### Step 4 - Convert TRUE to a unique number

IF(((COUNTIF($B$7:$E$10, $B$2:$D$4)=0)+COUNTIF(B12:$B$12, $B$2:$D$4))=1, (ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1, "")

becomes

IF({FALSE, TRUE, FALSE;TRUE, TRUE, TRUE;TRUE, FALSE, TRUE}, (ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1, "")

becomes

IF({FALSE, TRUE, FALSE;TRUE, TRUE, TRUE;TRUE, FALSE, TRUE}, {2.33333333333333, 2.25, 2.2;3.33333333333333, 3.25, 3.2;4.33333333333333, 4.25, 4.2}, "")

and returns

{"", 2.25, "";3.33333333333333, 3.25, 3.2;4.33333333333333, "", 4.2}

#### Step 5 - Find smallest number in array

The MIN function returns the minimum number in the given array.

MIN(IF(((COUNTIF($B$7:$E$10, $B$2:$D$4)=0)+COUNTIF(B12:$B$12, $B$2:$D$4))=1, (ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1, ""))

becomes

MIN({"", 2.25, "";3.33333333333333, 3.25, 3.2;4.33333333333333, "", 4.2})

and returns 2.25

#### Step 6 - Find position in array

IF(MIN(IF(((COUNTIF($B$7:$E$10, $B$2:$D$4)=0)+COUNTIF(B12:$B$12, $B$2:$D$4))=1, (ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1, ""))=(ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1, $B$2:$D$4, "")

becomes

IF(2.25=(ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1, $B$2:$D$4, "")

becomes

IF(2.25={2.33333333333333, 2.25, 2.2;3.33333333333333, 3.25, 3.2;4.33333333333333, 4.25, 4.2}, $B$2:$D$4, "")

and returns {"","BB","";"","","";"","",""}.

#### Step 7 - Concatenate strings in array

The TEXTJOIN function returns values concatenated ignoring blanks in array.

TEXTJOIN("", TRUE, IF(MIN(IF(((COUNTIF($B$7:$E$10, $B$2:$D$4)=0)+COUNTIF(B12:$B$12, $B$2:$D$4))=1, (ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1, ""))=(ROW($B$2:$D$4)+(1/(COLUMN($B$2:$D$4)+1)))*1, $B$2:$D$4, ""))

becomes

TEXTJOIN("", TRUE, {"","BB","";"","","";"","",""})

and returns "BB" in cell B13.

### Get Excel *.xlsx file

### Compare category

This article demonstrates ways to extract shared values in different cell ranges, two and three cell ranges. The Excel 365 […]

Array formula in B15: =INDEX($B$3:$B$12, MATCH(0, COUNTIF($B$14:B14, $B$3:$B$12)+IF(((COUNTIF($D$3:$D$11, $B$3:$B$12)>0)+(COUNTIF($F$3:$F$12, $B$3:$B$12)>0))=2, 0, 1), 0)) Copy cell B15 and paste it to […]

This article shows how to compare two nonadjacent cell ranges and extract values that exist only in one of the […]

How would you figure out an unique list where the sum of in one column doesn't match the other column? […]

The image above demonstrates an array formula in cell B11 that extracts values that only exist in List 1 (B3:B7) […]

I will in this blog post demonstrate a formula that extracts common records (shared records) from two data sets in […]

This article explains how to extract values that exist in three different columns, they must occur in each of the […]

Overview Updating a list using copy/paste is a boring task. This blog article describes how to update values in a price […]

Today I am going to show you how to quickly compare two tables using Conditional Formatting (CF). I am going […]

This article demonstrates formulas that extract values that exist only in one column out of two columns. There are text […]

Question: How do i remove common values between two lists? Answer: The solution in this article, removes common values and […]

Question: i have two sets of data - one has an identifier column and one result column. A2 data1 B2 […]

The image above shows an array formula in cell B12 that extracts values shared by cell range B2:D4 (One) and […]

In this example we are going to use two lists with identical columns, shown in the image above. It is […]

This article describes an array formula that compares values from two different columns in two worksheets twice and returns a […]

## Functions in this article

More than 1300 Excel formulas

## Excel formula categories

## Excel categories

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

**Contact Oscar**

You can contact me through this contact form