Extracting common values from two cells containing comma-separated values in Excel

Question

Is there a simple way to extract common numbers from two cells with comma-separated numbers?

I have cells with 12 comma separated numbers in each cell. (They are not all unique. Some numbers can be repeated twice. but never more than twice. Numbers are all positive, and one or two digit numbers only)

My data is like so: they are in column A:

11,11,13,15,16,18,20,20,26,27,28,29
8,9,10,12,13,14,18,20,21,22,24,28
13,13,14,14,15,17,18,19,20,21,23,25
6,6,8,10,12,14,15,17,18,20,20,25
11,13,17,18,19,19,22,25,26,28,28,31
7,9,15,16,17,18,23,24,24,25,26,27
7,9,11,12,12,15,16,16,18,18,20,23
9,11,13,15,18,22,23,24,25,28,29,29
7,9,10,11,12,12,13,14,15,16,19,22
5,10,11,12,12,16,17,18,20,22,24,25
7,10,13,16,16,17,18,19,21,23,24,24
10,14,16,18,18,19,21,23,23,25,27,28

The result I would like to have is like so:

I need a solution without separating values into different columns, please. Thanks for your help.

Since there can be numbers repeating twice in some cases, I am also open to a solution like this, too.

Answer 1

Matching Sub Strings

Here 's the easier 'duplicates' solution:

In Excel use it like this:

=comStr(A2,A3)

Copy the code into a standard module e.g. Module1

The Code

Option Explicit

Function comStr(String1 As String, _
                 String2 As String, _
                 Optional ByVal Delimiter As String = ",") _
         As String
         
    Dim Data1, Data2, Result(), i As Long, j As Long, l As Long
    Data1 = Split(String1, Delimiter)
    Data2 = Split(String2, Delimiter)
    For i = 0 To UBound(Data1)
        For j = 0 To UBound(Data2)
            If Data1(i) = Data2(j) Then GoSub writeResult: Exit For
        Next j
    Next i
                    
    comStr = Join(Result, Delimiter)

    Exit Function

writeResult:
    ReDim Preserve Result(l)
    Result(l) = Data1(i)
    l = l + 1
    Return

End Function

EDIT:

Here is the 'full' version where you can choose if duplicates are allowed.

In Excel use it like this:

=comStr(A2,A3,TRUE) to allow duplicates (like in the version above) or =comStr(A2,A3) or =comStr(A2,A3,FALSE) to not allow them.

Function comStr(String1 As String, _
                 String2 As String, _
                 Optional allowDupes As Boolean = False, _
                 Optional ByVal Delimiter As String = ",") _
         As String
         
    Dim Data1, Data2, Result(), Curr, i As Long, j As Long, l As Long, n As Long
    Data1 = Split(String1, Delimiter)
    Data2 = Split(String2, Delimiter)
    For i = 0 To UBound(Data1)
        Curr = Data1(i)
        For j = 0 To UBound(Data2)
            If Data2(j) = Curr Then GoSub writeResult: Exit For
        Next j
    Next i
                    
    If l = 0 Then Exit Function
    comStr = Join(Result, Delimiter)

    Exit Function

writeResult:
    If Not allowDupes Then
        If l > 0 Then
            For n = 0 To l - 1
                If Result(n) = Curr Then Exit For
            Next
            If n <= l - 1 Then Return
        End If
    End If
    ReDim Preserve Result(l)
    Result(l) = Data1(i)
    l = l + 1
    Return

End Function