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

#### Table of Contents

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

Excel 365 formula in cell B13:

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

Filter values existing in Range 1 but not in Range 2.xlsx

## 2. Filter not shared values out of two cell ranges - UDF

This post describes a custom function (User defined Function) that extract values existing only in one out of two cell ranges, see picture above. For example, value II is not extracted because it exists in both cell ranges Range1 and Range2.

This UDF is useful if you have lots of data and a formula is too slow.

Array formula in cell range B9: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.

### Excel user defined function

Function Filter_Values(rng1 As Variant, rng2 As Variant) As Variant ' This udf filter values that exists only in one out of two ranges Dim Value As Variant Dim temp() As Variant Dim Test1 As New Collection Dim Test2 As New Collection ReDim temp(0) rng1 = rng1.Value rng2 = rng2.Value On Error Resume Next For Each Value In rng1 If Len(Value) > 0 Then Test1.Add Value, CStr(Value) Next Value For Each Value In rng2 If Len(Value) > 0 Then Test2.Add Value, CStr(Value) Next Value Err = False For Each Value In Test2 If Len(Value) > 0 Then Test1.Add Value, CStr(Value) If Err Then Test1.Remove Value End If Err = False Next Value For Each Value In Test1 temp(UBound(temp)) = Value ReDim Preserve temp(UBound(temp) + 1) Next Value On Error GoTo 0 Filter_Values = Application.Transpose(temp) End Function

### How to add the User defined Function to your workbook

1. Press Alt-F11 to open visual basic editor

2. Press with left mouse button on Module on the Insert menu

3. Copy and paste the above user defined function

4. Exit visual basic editor

5. Select sheet1

6. Select cell range A1:A5000

7. Type =Filter_Values(Sheet2!A1:J500, Sheet3!A1:J500) into formula bar and press CTRL+SHIFT+ENTER

The excel file contains 5000 random values in each range.

### Compare category

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

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

This article demonstrates techniques to highlight differences and common values across lists. What's on this page How to highlight differences […]

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