comparing text cells

[Guest]

I want to compare two lists of comma delimited text items in two separate
cells and then determine which items are in one cell and not in the other,
and visa versa.

The available functions do not seem to support this, except EXACT, which
simply returns TRUE or FALSE. I want the items that represent the difference
between the cells.

 

[Guest]

You may use and UDF like this:

Function CompareStr(rng1 As Range, rng2 As Range)
Application.Volatile
Dim rng1Values, rng2Values, d, result, i
'The exists method of the dictionary object will be useful here
Set d = CreateObject("Scripting.Dictionary")
'Create arrays with the range values
rng1Values = Split(rng1.Value, ",")
rng2Values = Split(rng2.Value, ",")
'Loop over the values of the second array, and fill the dictionary
For i = 0 To UBound(rng2Values)
d.Add rng2Values(i), 1
Next
'Loop over the values of the first array, and check if they are in the
dictionary
For i = 0 To UBound(rng1Values)
'If the value is not present, add it to the result
If Not d.exists(rng1Values(i)) Then
result = result & "," & rng1Values(i)
End If
Next
'Present the result, if any
If Len(result) > 0 Then
CompareStr = Right(result, Len(result) - 1)
Else
CompareStr = 0
End If
End Function

This function needs two cell references, and it will show the elements on
the first cell that are not in the second one. If you want to have the other
way, just swap the references.

Hope this helps,
Miguel.

 

[Guest]

Thank you Miguel!

You may use and UDF like this:

Function CompareStr(rng1 As Range, rng2 As Range)
Application.Volatile
Dim rng1Values, rng2Values, d, result, i
'The exists method of the dictionary object will be useful here
Set d = CreateObject("Scripting.Dictionary")
'Create arrays with the range values
rng1Values = Split(rng1.Value, ",")
rng2Values = Split(rng2.Value, ",")
'Loop over the values of the second array, and fill the dictionary
For i = 0 To UBound(rng2Values)
d.Add rng2Values(i), 1
Next
'Loop over the values of the first array, and check if they are in the
dictionary
For i = 0 To UBound(rng1Values)
'If the value is not present, add it to the result
If Not d.exists(rng1Values(i)) Then
result = result & "," & rng1Values(i)
End If
Next
'Present the result, if any
If Len(result) > 0 Then
CompareStr = Right(result, Len(result) - 1)
Else
CompareStr = 0
End If
End Function

This function needs two cell references, and it will show the elements on
the first cell that are not in the second one. If you want to have the other
way, just swap the references.

Hope this helps,
Miguel.