Counting the common unique values between two comma-separated lists

Usercode

Board Regular
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

Last edited by a moderator:

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Yongle

Well-known Member
Try this UDF as a formula in B1
=GetCommonUnique(A1,A2)

VBA Code:
``````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

Board Regular
Thanks a lot, Yongle !

Peter_SSs

MrExcel MVP, Moderator
Another UDF to consider

VBA Code:
``````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
AB
112,12,13,14,16,17,203
211,13,17,18,18,20
Sheet2 (5)
Cell Formulas
RangeFormula
B1B1=CommonUnique(A1,A2)

Yongle

Well-known Member

@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

MrExcel MVP, Moderator
@Peter_SSs

=CommonUnique(A1,A2) returns 3 from these numbers
Thanks @Yongle, you are right - plenty of circumstances where it fails.

I have had another go though at doing it with just string manipulation.

VBA Code:
``````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

Board Regular

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

Well-known Member
@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

Board Regular
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

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

26Aug19.xlsx
AB
112,12,13,14,16,17,20,12,15,164
211,13,17,18,18,20,12
Sheet56
Cell Formulas
RangeFormula
B1B1=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))

Replies
10
Views
168
Replies
9
Views
150
Replies
2
Views
235
Replies
26
Views
987
Replies
8
Views
164