Excel udf: Filter values existing only in one out of two ranges
Filed in Compare, Excel, User defined functions (udf) on Nov.15, 2010. Email This article to a Friend
This post describes how to filter values existing only in one out of two cell ranges. See picture.
This udf is not case sensitive
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 implement user defined function in excel
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 sample file for this tutorial.
Compare two ranges and extract not common values-udf.xls (0,9 MB)
(Excel 97-2003 Workbook *.xls)







Leave a Reply