Counting the common unique values between two comma-separated lists

[Usercode]

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

 

[Amit Tandon]

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
	A	B	C
1	12,12,13,14,16,17,20,12,15,16	4	4
2	11,13,17,18,18,20,12
3
4	12,12,13,14,16,17,20	3	3
5	11,13,17,18,18,20
6
7	11,12,13,145,7,0,1234,1	5	5
8	12,0,1234,145,99,55,66,77,1,2,3
Sheet56 (2)

Cell Formulas
Range	Formula
B1,B7,B4	B1	=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,C4	C1	=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),"")))

 

[Peter_SSs]

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
	A	B	C
1	12,12,13,14,16,17,20,12,15,16	4	4
2	11,13,17,18,18,20,12,1145
3
4	12,12,13,14,16,17,20	3	3
5	11,13,17,18,18,20
6
7	11,12,13,145,7,0,1234,1	5	5
8	12,0,1234,145,99,55,66,77,1,2,3
9
10	12,13,14	1	0
11	13
Formula

Cell Formulas
Range	Formula
B1,B10,B7,B4	B1	=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,C4	C1	=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]

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
	A	B	C
1	12,12,13,14,16,17,20,12,15,16	4	4
2	11,13,17,18,18,20,12,1145
3
4	12,12,13,14,16,17,20	3	3
5	11,13,17,18,18,20
6
7	11,12,13,145,7,0,1234,1	5	5
8	12,0,1234,145,99,55,66,77,1,2,3
9
10	12,13,14	1	0
11	13
Formula

Cell Formulas
Range	Formula
B1,B10,B7,B4	B1	=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,C4	C1	=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
	A	B	C
1	12,12,13,14,16,17,20,12,15,16	4	4
2	11,13,17,18,18,20,12
3
4	12,12,13,14,16,17,20	3	3
5	11,13,17,18,18,20
6
7	11,12,13,145,7,0,1234,1	5	5
8	12,0,1234,145,99,55,66,77,1,2,3
9
10	12,13,14	1	1
11	13
12
13	Red, Blue, Green	2	2
14	Blue, White,Red,Black
Sheet56 (2)

Cell Formulas
Range	Formula
B1,B13,B10,B7,B4	B1	=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,C4	C1	=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]

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.