compare name in cells

[Dan]

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

 

[Patrick Molloy]

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

 

[Patrick Molloy]

change this

Set rng = Intersect(Target, Range("E:E"))

to

Set rng = Intersect(Target, Range("E:E;H:Q"))

 

[Patrick Molloy]

oops. replied to wrong mail.sorry

change this

Set rng = Intersect(Target, Range("E:E"))

to

Set rng = Intersect(Target, Range("E:E;H:Q"))