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

### Download Excel file through email

Enter your email address to receive the workbook. Note, an email will actually be sent to you.Extract unique distinct values from a filtered Excel defined Table [UDF and Formula]

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]

How to count word frequency in a cell range [UDF]

This user defined function creates a unique distinct list of words and how many times they occur in the selected […]

List files in a folder and subfolders [UDF]

This article demonstrates a user defined function that lists files in a ggiven folder and subfolders. A user defined function is […]

Split words in a cell range into a cell each [UDF]

This post describes how to split words in a cell range into a cell each using a custom function. I […]

Split values equally into groups

Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]

Filter unique distinct words from a cell range [UDF]

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

Count comma separated values [UDF]

I received an email from one of my seven blog readers. In Excel, I have a column, say A, with some […]

Lookup multiple values in one cell [UDF]

Chrisa asks: Hi Oscar...this is a very interesting function and helped me a lot so far. My file though is […]

The "Text to columns" feature in excel lets you split multiple values in a single cell using a delimiting character, […]

List permutations without repetition [UDF]

This blog post describes how to create permutations, repetition is NOT allowed. Permutations are items arranged in a given order meaning […]

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