try this User Defined Function:
Option Explicit
Function compare(text1 As String, text2 As String) As String
'find items in text2 that are not in text1
Dim vtext2 As Variant
Dim vtext1 As Variant
Dim res As String
Dim txt As String
Dim index1 As Long
Dim index2 As Long
Dim bFound As Boolean
vtext1 = Split(text1, ",")
vtext2 = Split(text2, ",")
For index2 = LBound(vtext2, 1) To UBound(vtext2, 1)
txt = ""
bFound = False
For index1 = LBound(vtext1, 1) To UBound(vtext1, 1)
If vtext1(index1) = vtext2(index2) Then
bFound = True
Exit For
End If
Next
If Not bFound Then
res = res & "," & vtext2(index2)
End If
Next
compare = Mid(res, 2)
End Function
"Dan" wrote:
> I have 2 cells that I need to compare
> In each cell there are name of student separated by coma
> so A1= Bill,Marco A2= Bill,John,Marco,Bella
>
> How can I have the unmatching names (name in A2 but not in A1) in A3
> So A3 should be John,Bella
>
> Any idea?
> Thanks
> Dan
>
|
Similar Threads
