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.
(Excel 97-2003 Workbook *.xls)