PDA

View Full Version : Compare cells



Tom Jones
05-15-2018, 08:12 AM
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
05-15-2018, 11:09 AM
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
05-15-2018, 11:52 AM
Thank you so much SamT for VBA code. It works very well.
Can you help me with a formula?

SamT
05-15-2018, 01:29 PM
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
05-15-2018, 11:03 PM
Thank you.