# 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

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
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))

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.

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.

### Which adblocker are you using?

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

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