Counting the common unique values between two comma-separated lists

Usercode

Board Regular
Joined
Aug 18, 2017
Messages
107
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:

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
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
54,435
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,977
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
54,435
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,977
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))
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,601
Messages
5,838,300
Members
430,537
Latest member
Antonio11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top