Counting the common unique values between two comma-separated lists

Usercode

Board Regular
Joined
Aug 18, 2017
Messages
104
Office Version
  1. 2016
Platform
  1. Windows
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:

Some videos you may like

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
Joined
Mar 11, 2015
Messages
6,868
Office Version
  1. 365
Platform
  1. Windows
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,784
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 11, 2015
Messages
6,868
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

@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
Joined
May 28, 2005
Messages
46,784
Office Version
  1. 365
Platform
  1. Windows
@Peter_SSs

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

Please ignore my previous UDF.
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
Joined
May 23, 2020
Messages
84
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Mar 11, 2015
Messages
6,868
Office Version
  1. 365
Platform
  1. Windows
@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
Joined
May 23, 2020
Messages
84
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 23, 2020
Messages
84
Office Version
  1. 365
Platform
  1. Windows
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,112,816
Messages
5,542,660
Members
410,567
Latest member
SCraig123
Top