Counting the common unique values between two comma-separated lists

[Usercode]

I need help with a formula to count the unique values in two cells with comma separated values.

My data is like this in column A:

12,12,13,14,16,17,20
11,13,17,18,18,20

The expected result for these in column B1: 3


Note: The values in each comma-separated list all are positive but may contain duplicates.

I am open to a vba solution too. Thank you.

Also asked here Counting the common unique values between two comma-separated lists

 

[Yongle]

Try this UDF as a formula in B1
=GetCommonUnique(A1,A2)

Function GetCommonUnique(ByVal A As Range, ByVal B As Range) As Long
    Dim x As Variant, y As Variant
    Dim coll As Collection: Set coll = New Collection
    On Error Resume Next
    For Each x In Split(A, ",")
        For Each y In Split(B, ",")
            If y = x Then coll.Add CStr(x), CStr(x)
        Next y
    Next x
    On Error GoTo 0
    
    GetCommonUnique = coll.Count
End Function

 

[Usercode]

Thanks a lot, Yongle !

 

[Peter_SSs]

Another UDF to consider

Function CommonUnique(s1 As String, s2 As String) As Long
  Dim itm As Variant
 
  s1 = "," & s1 & ","
  s2 = "," & s2 & ","
  For Each itm In Split(s1, ",")
    If InStr(1, s2, "," & itm & ",") > 0 And InStr(1, s1, "," & itm & ",") = InStrRev(s1, "," & itm & ",") Then CommonUnique = CommonUnique + 1
  Next itm
End Function

Usercode 2020-07-21 1.xlsm
	A	B
1	12,12,13,14,16,17,20	3
2	11,13,17,18,18,20
Sheet2 (5)

Cell Formulas
Range	Formula
B1	B1	=CommonUnique(A1,A2)

 

[Yongle]

@Peter_SSs

=CommonUnique(A1,A2) returns 3 from these numbers
12,12,13,14,16,17,20,12,15,16
11,13,17,18,18,20,12

 

[Peter_SSs]

@Peter_SSs

=CommonUnique(A1,A2) returns 3 from these numbers

Thanks @Yongle, you are right - plenty of circumstances where it fails.

Please ignore my previous UDF.
I have had another go though at doing it with just string manipulation. ?

Function CountCommon(s1 As String, s2 As String) As Long
  s1 = " " & Replace(s1, ",", "  ") & " "
  s2 = " " & Replace(s2, ",", " ") & " "
  Do Until Len(s1) = 2
    If InStr(1, s2, Left(s1 & " ", InStr(2, s1, " "))) > 0 Then CountCommon = CountCommon + 1
    s1 = " " & Replace(Application.Trim(Replace(s1, Left(s1 & " ", InStr(2, s1, " ")), " ")), " ", "  ") & " "
  Loop
End Function

 

[Amit Tandon]

In case you may want to use formulas:

=SUM(--ISNUMBER(MATCH(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),(ROW(INDIRECT("1:"&LEN(A1)-LEN(TRIM(SUBSTITUTE(A1,",", "")))+1))-1)*LEN(A1)+1,LEN(A1))),TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))),(ROW(INDIRECT("1:"&LEN(A2)-LEN(TRIM(SUBSTITUTE(A2,",", "")))+1))-1)*LEN(A2)+1,LEN(A2))),0)))

Note: INDIRECT is a volatile function and may increase processing time depending on your file size.

 

[Yongle]

@Amit Tandon
- your formula returns 3 with these values in A1 & A2

12,12,13,14,16,17,20,12,15,16
11,13,17,18,18,20,12

 

[Amit Tandon]

Array formula if not using Office 365:

=SUM(--(FREQUENCY(IFERROR(MATCH(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),(ROW(INDIRECT("1:"&LEN(A1)-LEN(TRIM(SUBSTITUTE(A1,",", "")))+1))-1)*LEN(A1)+1,LEN(A1))),TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))),(ROW(INDIRECT("1:"&LEN(A2)-LEN(TRIM(SUBSTITUTE(A2,",", "")))+1))-1)*LEN(A2)+1,LEN(A2))),0),""),ROW(INDIRECT("1:"&LEN(A1)-LEN(TRIM(SUBSTITUTE(A1,",", "")))+1)))>0))

 

[Amit Tandon]

Use Array if not Office 365 - posting by using XL2BB:

26Aug19.xlsx
	A	B
1	12,12,13,14,16,17,20,12,15,16	4
2	11,13,17,18,18,20,12
Sheet56

Cell Formulas
Range	Formula
B1	B1	=SUM(--(FREQUENCY(IFERROR(MATCH(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),(ROW(INDIRECT("1:"&LEN(A1)-LEN(TRIM(SUBSTITUTE(A1,",", "")))+1))-1)*LEN(A1)+1,LEN(A1))),TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))),(ROW(INDIRECT("1:"&LEN(A2)-LEN(TRIM(SUBSTITUTE(A2,",", "")))+1))-1)*LEN(A2)+1,LEN(A2))),0),""),ROW(INDIRECT("1:"&LEN(A1)-LEN(TRIM(SUBSTITUTE(A1,",", "")))+1)))>0))