How to Identify Values in Common Between 2 cells

I have a spreadsheet that contains cells that each have a range of values.
For instance, 1 cell might have the values of 1-15. The next cell might be
11-20. I want to set up another table that compares each combination of 2
cells, and identifies the common values. In this example, that would be
11-15. The answer could either be expressed as 11-15 or 5, with 5 being the
number of numbers in common between the 2 cells' values. I would prefer 5.
I am new to this, and am hopeful that someone has dealt with this need
before. Thank you for your assistance.

When you say one cell has the values 1-15, is it literally "1-15" or is it 1,
2, 3, 4, 5, 6, etc (all in one cell)?

Assuming the first scenario:
A1 = 1 - 15
A2 = 11 - 20

try:
=SUM(--ISNUMBER(MATCH(ROW(INDIRECT(--LEFT(A2,FIND("-",A2)-1)&":"&--RIGHT(A2,LEN(A2)-FIND("-",A2)))),ROW(INDIRECT(--LEFT(A1,FIND("-",A1)-1)&":"&--RIGHT(A1,LEN(A1)-FIND("-",A1)))),0)))

entered with Control+Shift+Enter.

If the second, I would probably try to split the data so that one number was
in a cell. Say F1:F15 = 1 through 15 and G1:G10 = 11 through 20, then
=SUM(--ISNUMBER(MATCH(F1:F15,G1:G10,0)))
again entered with Control+Shift+Enter.

Or, maybe use VBA to create a user defined function:

Function Test(rng1 As Range, _
rng2 As Range) As Long
Dim var1 As Variant
Dim var2 As Variant
Dim i As Long

var1 = Split(rng1.Value, ",", -1, vbTextCompare)
var2 = Split(rng2.Value, ",", -1, vbTextCompare)

For i = LBound(var1) To UBound(var1)
If IsNumeric(Application.Match(var1(i), var2, 0)) Then _
Test = Test + 1
Next i
End Function


syntax is =Test(A1, A2). You could change the name of the function to
whatever you want.


"ahofeld" wrote:

> I have a spreadsheet that contains cells that each have a range of values.
> For instance, 1 cell might have the values of 1-15. The next cell might be
> 11-20. I want to set up another table that compares each combination of 2
> cells, and identifies the common values. In this example, that would be
> 11-15. The answer could either be expressed as 11-15 or 5, with 5 being the
> number of numbers in common between the 2 cells' values. I would prefer 5.
> I am new to this, and am hopeful that someone has dealt with this need
> before. Thank you for your assistance.