PDA

View Full Version : [SOLVED] Extracting intersecting values from two or more ranges



Mati44
05-05-2018, 08:12 AM
I am looking for a formula or code to extract intersecting numbers from two or more range of values. Lets say, I have values in A1 ( 1,2,3,4,5) and B1 (1,2,3) and C1 (2,3). Is it possible to get the intersecting values (2,3) of these three groups in D1? In this example, I used three groups but there can be more. Thanks.

mana
05-05-2018, 07:21 PM
Option Explicit


Function CommonValue(r As Range) As String
Dim d As Object
Dim c As Range
Dim i As Long, k As Long
Dim tmp, v

Set d = CreateObject("scripting.dictionary")

For Each c In r
d(d.Count + 1) = Split(c.Value, ",")
Next

tmp = d(1)

For i = 2 To d.Count
v = tmp
For k = 0 To UBound(d(i))
v = Filter(v, d(i)(k), False)
Next
For k = 0 To UBound(v)
tmp = Filter(tmp, v(k), False)
Next
Next


CommonValue = Join(tmp, ",")

End Function

Mati44
05-05-2018, 11:11 PM
Hi Mana, thanks for your solution. It works perfectly!