# Developers Planet

ArthurV February 2016

### compare 3 ranges instead of 2

``````Public Function Compare(r1 As Range, r2 As Range) As Long
Dim r As Range, v As Variant, v2 As Variant
Dim rr As Range
For Each r In r1
v = r.Value
If v <> 0 And v <> "" Then
For Each rr In r2
v2 = rr.Value
If v = v2 Then Compare = Compare + 1
Next rr
End If
Next r
End Function
``````

This UDF compares 2 ranges and return the number of matched values. I would like to compare 3 ranges instead, in order to find how many values appeared in all 3 ranges simultaneously.

Much appreciate any help.

Elbert Villarreal February 2016

This functions works fine for me, tell me if you need some improvement.

``````Public Function Compare(r1 As Range, r2 As Range, r3 As Range) As Long
Dim i
Dim v1
Dim v2
Dim v3
Dim counter

counter = 0
For Each i In r1
counter = counter + 1
v1 = r1(counter).Value
v2 = r2(counter).Value
v3 = r3(counter).Value

If v1 = v2 And v2 = v3 Then
'r3(counter).Offset(0, 2).Value = "OK" 'this is for the test
Compare = Compare + 1
'I think could be easy to test and return a value...
'Compare = v1 'Because is the same value in the 3 cells
Else
'r3(counter).Offset(0, 2).Value = "NO"'this is for the test
'Do another code...

End If
Next i
End Function
``````

Edit #1

This could help...

``````Public Function Compare2(r1 As Range, r2 As Range, r3 As Range) As Long
Dim i
Dim v1
Dim v2
Dim v3
Dim counter
Dim n1 As Range
Dim n2 As Range
Dim n3 As Range
Dim max

counter = 0
max = Application.WorksheetFunction.max(r1.Count, r2.Count, r3.Count)
'With "max" take the max number of rows in the range to use it

Set n1 = Range(Cells(r1(1).Row, r1(1).Column), Cells(r1(1).Row + max - 1, r1(1).Column))
Set n2 = Range(Cells(r2(1).Row, r2(1).Column), Cells(r2(1).Row + max - 1, r2(1).Column))
Set n3 = Range(Cells(r3(1).Row, r3(1).Column), Cells(r3(1).Row + max - 1, r3(1).Column))
'Here we set new ranges, equals all of them, to use them in the for loop and compare
'we use the ref of the input ranges.

counter = 0
For Each i In n1
counter = counter + 1 'this is the index for the new ranges
v1 = n1(counter).Value 'store every value of the new ranges
v2 = n2(counter).Value
v3 = n3(counter).Value

If v1 = v2 And v2 = v3 Then 'do the comparison, and if the 3 values are equal

``````
``` ```
``` ```
``` Tim Williams February 2016 Public Function Compare(r1 As Range, r2 As Range, r3 As Range) As Long Dim r As Range, v As Variant, m1 As Variant, m2 As Variant Dim rv As Long rv = 0 For Each r In r1 v = r.Value If v <> 0 And v <> "" And Not IsError(v) Then m1 = Application.Match(v, r2, 0) m2 = Application.Match(v, r3, 0) If Not IsError(m1) And Not IsError(m2) Then rv = rv + 1 End If End If Next r Compare = rv End Function tigeravatar February 2016 Here's an alternative for a non-vba solution. Consider a data layout like so: In cell E2 is this formula: =SUMPRODUCT(--(COUNTIF(B2:B16,A2:A23)>0),--(COUNTIF(C2:C19,A2:A23)>0)) I have highlighted all of the cells that have matches in all three columns for clarity. There are a total of 8 cells in column A that have duplicates found in columns B and C. Note that this will count duplicates in column A (but so does your UDF already). ```
``` ```
``` ```
``` Post Status Asked in February 2016Viewed 1,434 timesVoted 4Answered 3 times Search Leave an answer ```
``` ```
``` ```
``` Quote of the day: live life .btn-primary{ background-color: #f44336 !important; border-color: #f44336 !important; } Devs Planet ® 2014-2016 www.devsplanet.com Devs Planet © all rights reserved Quick Actions Search // Used to toggle the menu on small screens when clicking on the menu button function myFunction() { var x = document.getElementById("navDemo"); if (x.className.indexOf("w3-show") == -1) { x.className += " w3-show"; } else { x.className = x.className.replace(" w3-show", ""); } } ```