Compare cells

[Tom Jones]

Hi,

In cell A2 there are 15 comma-separated numbers, in cell B2 there are between 3 and 8 comma-separated numbers. What formula should be in cell C2 where I list the common numbers in A2 and B2, separated by commas.

Thank you.

[SamT]

What formula should be in cell C2

=FindCommons(A2,B2)

Place this Code in a Standard Module

Public Sub FindCommons(Cell1 as Range, Cell2 As Range) As String
Dim Arr1 As Variant
Dim Arr2 As Variant
Dim 1 As LOng
Dim j As Long
dim Tmp As STring

Arr1 = Split(Cell1, ",") 
Arr2 = Split(Cell2, ",")
For i = lbound(Arr1) to ubound(arr1)
For j = lbound(Arr2) to ubound(Arr2)
   If Arr1(i) = Arr2(j) THen 
      Tmp = Tmp & Trim(Arr2(j)) & ", "
   End If
Next: Next

FindCommons = Left(Tmp, Len(Tmp) - 2) 'Lose last ", "
End Function

[Tom Jones]

Thank you so much SamT for VBA code. It works very well.
Can you help me with a formula?

[SamT]

Can you help me with a formula?

I never got into Excel Formulas. Maybe somebody will be along who can help with that. Sorry.

[Tom Jones]

Thank you.