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

How to find common values from two lists

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

Compare two lists of data: Filter common row records in 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

Compare pricelists in excel 2007

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

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

Compare two tables in excel: Remove common rows

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

Filter values that exists in all three lists

Peter Voss asks: This is close to what I need. I have three lists of email addresses. If an email […]Comments(5) Filed in category: Compare, Excel

Compare and filter values between two lists in excel

Question: Hi, I have seen a lot of examples related to unique list ... mais not found what I need. […]Comments(4) Filed in category: Compare, Excel

What values are missing in List 1 that exists i List 2?

Question: How to filter out data from List 1 that is missing in list 2? Answer: This formula is useful […]Comments(4) Filed in category: Compare, Excel, Missing values

Filter common values from three columns in excel

Question: How do I filter values that exists in all three columns? Answer: Formula in A14: =INDEX(List1, MATCH(0, COUNTIF($A$13:A13, List1)+IF(IF(COUNTIF(List2, […]Comments(4) Filed in category: Compare, Excel

Compare values between two columns and filter values existing in only one column

There are text values in column A and column B. The question is how do you compare the values in […]Comments(3) Filed in category: Compare, Excel

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