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 2016

Viewed 1,434 times

Voted 4

Answered 3 times
#### Search

## Leave an answer

```
```

```
```