## Excel udf: Filter common values between two cell ranges in excel

I tried the array formula in this post: Filter common values between two ranges using array formula in excel to extract common values between two cell ranges. 40000 random cell values in each cell range. As you might have guessed, the array formula is too slow.

Sheet2 contains 40000 random text strings in cell range A1:J4000

Sheet3 also contains 40000 random text strings in cell range A1:J4000

This udf creates a list of common cell values between the two cell ranges.

### User defined function

Function Common_Values_2_Ranges(rng1 As Variant, rng2 As Variant) As Variant Dim Value1 As Variant Dim Value2 As Variant Dim temp() As Variant Dim Test As New Collection ReDim temp(0) rng1 = rng1.Value rng2 = rng2.Value On Error Resume Next For Each Value1 In rng1 If Len(Value1) > 0 Then Test.Add Value1, CStr(Value1) Next Value1 On Error GoTo 0 On Error Resume Next For Each Value2 In rng2 If Len(Value2) > 0 Then Test.Add Value2, CStr(Value2) If Err Then temp(UBound(temp)) = Value2 ReDim Preserve temp(UBound(temp) + 1) End If Err = False Test.Remove Value2 Next Value2 On Error GoTo 0 Common_Values_2_Ranges = Application.Transpose(temp) End Function

### How to implement user defined function in excel

- Press Alt-F11 to open visual basic editor
- Click Module on the Insert menu
- Copy and paste the above user defined function
- Exit visual basic editor
- Select sheet1
- Select cell range A1:A5000
- Type =Common_Values_2_ranges(Sheet2!A1:J4000, Sheet3!A1:J4000) into formula bar and press CTRL+SHIFT+ENTER

### Download excel sample file for this tutorial.

Filter-common-values-in-two-ranges-udf.zip (~600 KB)

(Excel 97-2003 Workbook *.xls)

### Recommended blog post:

Compare two lists of data: Filter common row records in excel

### Category: Compare

Question: How can I compare two columns to find common values? Array formula in C2: =INDEX($A$2:$A$11, SMALL(IF(COUNTIF($B$2:$B$11, $A$2:$A$11), MATCH(ROW($A$2:$A$11),ROW($A$2:$A$11)), ""), […]

Comments(13) Filed in category: Compare, Excel

This post describes how to compare two price lists in excel 2007. I am going to create three conditional formatting […]

Comments(7) Filed in category: Compare, Excel

This blog post describes how to extract common rows (records) from two lists in excel 2007. Sheet: List 1 Sheet: […]

Comments(7) Filed in category: Compare, Excel, Records

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

Comments(5) Filed in category: Compare, Excel, Unique distinct values

Table of Contents Compare two tables: Remove common rows Compare two tables using a condition Let me demonstrate how to extract […]

Comments(5) Filed in category: Compare, Records

### Category: User defined functions udf

UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you […]

Comments(26) Filed in category: Conditional formatting, Count values, Excel, User defined functions (udf), VBA

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

Comments(17) Filed in category: Excel, Sort values, Unique distinct values, User defined functions (udf), VBA

This user defined function creates an unique list of words and their frequency in selected range. User defined function: =FreqWords(cell_range, […]

Comments(16) Filed in category: Count values, Excel, Frequency, User defined functions (udf), VBA

Sometimes you need to find formulas containing literals (hard coded values) in a workbook. I found this excellent UDF in […]

Comments(12) Filed in category: Excel, User defined functions (udf), VBA

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

Comments(11) Filed in category: Excel, Unique distinct values, User defined functions (udf), VBA

### 2 Responses to “Excel udf: Filter common values between two cell ranges in excel”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

I am running your UDF function and not sure what Err does. When you test for If Err, what specific condition are you looking for here?

Ekaterina Boehm

Err checks if there was an error adding the text string to the collection.

It means that the text string is already in the collection so it must be a value found in both cell ranges.