# 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:

#### Amit Tandon

##### Board Regular
To consolidate the replies:

The 1st formula did not count Unique common occurrences. To count unique common occurrences, there are 2 options as below - using FREQUENCY in cell B1 and using UNIQUE (applicable only for Office 365) in cell C1. If you are not using Office 365, then the FREQUENCY formula will be entered as an Array formula (Ctrl+Shift+Enter):

26Aug19.xlsx
ABC
112,12,13,14,16,17,20,12,15,1644
211,13,17,18,18,20,12
3
412,12,13,14,16,17,2033
511,13,17,18,18,20
6
711,12,13,145,7,0,1234,155
812,0,1234,145,99,55,66,77,1,2,3
Sheet56 (2)
Cell Formulas
RangeFormula
B1,B7,B4B1=SUM(--(FREQUENCY(IFERROR(MATCH(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),(ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,",", ""))+1))-1)*LEN(A1)+1,LEN(A1))),TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))),(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2,",", ""))+1))-1)*LEN(A2)+1,LEN(A2))),0),""),ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2,",", ""))+1)))>0))
C1,C7,C4C1=COUNT(UNIQUE(IFERROR(MATCH(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),(ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,",", ""))+1))-1)*LEN(A1)+1,LEN(A1))),TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))),(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2,",", ""))+1))-1)*LEN(A2)+1,LEN(A2))),0),"")))

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### Peter_SSs

##### MrExcel MVP, Moderator
To consolidate the replies:
It may not be possible with the OPs data but noting that both formulas return 0 if the second cell only contains one number even if it is a match to a number in the first string.

using UNIQUE (applicable only for Office 365)
Obviously not applicable for this OP as they do not have 365 but since you mentioned a 365 solution, there is also this shorter & non-volatile option shown in column B below.

Usercode 2020-07-21 1.xlsm
ABC
112,12,13,14,16,17,20,12,15,1644
211,13,17,18,18,20,12,1145
3
412,12,13,14,16,17,2033
511,13,17,18,18,20
6
711,12,13,145,7,0,1234,155
812,0,1234,145,99,55,66,77,1,2,3
9
1012,13,1410
1113
Formula
Cell Formulas
RangeFormula
B1,B10,B7,B4B1=COUNT(FIND(","&UNIQUE(MID(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),SEQUENCE(LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1,,,LEN(A1)),LEN(A1))+0)&",",","&A2&","))
C1,C10,C7,C4C1=COUNT(UNIQUE(IFERROR(MATCH(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),(ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,",", ""))+1))-1)*LEN(A1)+1,LEN(A1))),TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))),(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2,",", ""))+1))-1)*LEN(A2)+1,LEN(A2))),0),"")))

#### Amit Tandon

##### Board Regular
It may not be possible with the OPs data but noting that both formulas return 0 if the second cell only contains one number even if it is a match to a number in the first string.

Obviously not applicable for this OP as they do not have 365 but since you mentioned a 365 solution, there is also this shorter & non-volatile option shown in column B below.

Usercode 2020-07-21 1.xlsm
ABC
112,12,13,14,16,17,20,12,15,1644
211,13,17,18,18,20,12,1145
3
412,12,13,14,16,17,2033
511,13,17,18,18,20
6
711,12,13,145,7,0,1234,155
812,0,1234,145,99,55,66,77,1,2,3
9
1012,13,1410
1113
Formula
Cell Formulas
RangeFormula
B1,B10,B7,B4B1=COUNT(FIND(","&UNIQUE(MID(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),SEQUENCE(LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1,,,LEN(A1)),LEN(A1))+0)&",",","&A2&","))
C1,C10,C7,C4C1=COUNT(UNIQUE(IFERROR(MATCH(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),(ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,",", ""))+1))-1)*LEN(A1)+1,LEN(A1))),TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))),(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2,",", ""))+1))-1)*LEN(A2)+1,LEN(A2))),0),"")))

1) My FREQUENCY formula will work well even if there is a single number in the 2nd cell - please enter as an Array formula as below.

2) Yes, Correct, using the new function of SEQUENCE, with UNIQUE (I have got these new functions in my Office 365 just 5 days back) will shorten and work well - your formula is presently working only for numbers separated with comma without space - have updated it to have it work with text (and spaces) also as below.

26Aug19.xlsx
ABC
112,12,13,14,16,17,20,12,15,1644
211,13,17,18,18,20,12
3
412,12,13,14,16,17,2033
511,13,17,18,18,20
6
711,12,13,145,7,0,1234,155
812,0,1234,145,99,55,66,77,1,2,3
9
1012,13,1411
1113
12
13Red, Blue, Green22
14Blue, White,Red,Black
Sheet56 (2)
Cell Formulas
RangeFormula
B1,B13,B10,B7,B4B1=SUM(--(FREQUENCY(IFERROR(MATCH(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),(ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,",", ""))+1))-1)*LEN(A1)+1,LEN(A1))),TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))),(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2,",", ""))+1))-1)*LEN(A2)+1,LEN(A2))),0),""),ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2,",", ""))+1)))>0))
C1,C13,C10,C7,C4C1=COUNT(FIND(","&UNIQUE(SUBSTITUTE(UNIQUE(MID(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),SEQUENCE(LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1,,,LEN(A1)),LEN(A1)))," ",""))&",",","&SUBSTITUTE(A2," ","")&","))
Press CTRL+SHIFT+ENTER to enter array formulas.

#### Peter_SSs

##### MrExcel MVP, Moderator
My FREQUENCY formula will work well even if there is a single number in the 2nd cell - please enter as an Array formula as below.
You are correct. I was of the understanding that Excel 365 with the dynamic array functions treated all formulas possible as array formulas without the need for C+S+E & I took this at face value ...
If you are not using Office 365, then the FREQUENCY formula will be entered as an Array formula (Ctrl+Shift+Enter)

your formula is presently working only for numbers separated with comma without space
Yes, I deliberately only allowed for that since that was the form of the OP's sample data.

Replies
10
Views
170
Replies
9
Views
150
Replies
2
Views
241
Replies
26
Views
1K
Replies
8
Views
166