Filter values in common between two cell ranges [UDF]
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 add the user defined function to your workbook
- 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
- Make sure you save your workbook with the file extension *.xlsm so you can use the UDF the next time you open the same workbook.
Recommended blog post:
Compare two lists of data: Filter common row records in excel
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 […]
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 […]
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 […]
Search for a file in folder and subfolders [UDF]
The image above demonstrates a user-defined function in cell range B6:D7 that allows you to search a folder and subfolders […]
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, […]
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 […]
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 unique distinct values by cell color
This article demonstrates a User Defined Function (UDF) that counts unique distinct cell values based on a given cell color. […]
Substitute multiple text strings [UDF]
The SUBSTITUTE and REPLACE functions can only handle one string, the following User-Defined Function (UDF) allows you to substitute multiple […]
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 […]
2 Responses to “Filter values in common between two cell ranges [UDF]”
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.
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.