URGENT find common string values in 2 colums

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook. Make sure the workbook demonstrates your sample data AND your desired results.

Here are two functions to accomplish this. One I wrote called CompareLists() and it is a UDF. Here's the code, it goes in a standard code module, not a sheet module:

Option Explicit

Function CompareLists(Rng1 As Range, Rng2 As Range, _
    Optional Delim As String, Optional Incl As Boolean) As String
'Compare the items in two delimited lists and make a list of items
'  Incl=TRUE: in both lists (common)
'  Incl=FALSE: NOT in both lists (uncommon)
Dim Arr As Variant, buf As String, n As Long

If Delim = "" Then Delim = ","
If Incl Then
    Arr = Split(Rng1.Cells(1), Delim)

    For n = LBound(Arr) To UBound(Arr)
        If InStr(Rng2, Arr(n)) > 0 And Len(Arr(n)) > 0 Then buf = buf & "," & Arr(n)
    Next n
Else
    Arr = Split(Rng1.Cells(1), Delim)
    If InStr(Rng2, Arr(n)) = 0 And Len(Arr(n)) > 0 Then buf = buf & "," & Arr(n)
    Arr = Split(Rng2.Cells(1), Delim)
    For n = LBound(Arr) To UBound(Arr)
        If InStr(Rng1, Arr(n)) = 0 And Len(Arr(n)) > 0 Then buf = buf & "," & Arr(n)
    Next n
End If

If Len(buf) > 0 Then
    buf = BubbleSort2(buf, Delim)
    CompareLists = Mid(buf, 2, Len(buf))
Else
    CompareLists = "no results"
End If

End Function


Function BubbleSort2(numString As String, Delim As String) As String
Dim Arr As Variant, vTemp As Variant
Dim i As Long, j As Long, buf As String

Arr = Split(numString, Delim)

'from John Walkenbach’s Excel Power Programming with VBA to sort the array
    For i = LBound(Arr, 1) To UBound(Arr, 1) - 1
        For j = i + 1 To UBound(Arr, 1)
            If Arr(i) > Arr(j) Then
                vTemp = Arr(i)
                Arr(i) = Arr(j)
                Arr(j) = vTemp
            End If
        Next j
    Next i

BubbleSort2 = WorksheetFunction.Transpose(Join(Arr, Delim))
End Function

Once installed you use it in a cell like any other formula, in C2:

=COMPARELISTS(A2, B2, ",", TRUE)


RANGE1 - the first delimited list in a cell. Only the first cell will be used if this is a multicell range
RANGE2 - the second delimited list in another cell. Only the first cell will be used if this is a multicell range
DELIMITER - an optional string that indicates the delimiter in each comparison cell, the default is comma
TRUE/FALSE - optional, if TRUE is a list of common items, if FALSE or omitted is a list of non-common items



The second function is a BubbleSort function using a popular technique that is used to "sort an array in vba", it is putting the results into alphabetical order as you requested.