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:

Amit Tandon

Board Regular
Joined
May 23, 2020
Messages
84
Office Version
  1. 365
Platform
  1. Windows
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),"")))
 

Some videos you may like

Excel Facts

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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,825
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 23, 2020
Messages
84
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
46,825
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,895
Messages
5,544,901
Members
410,643
Latest member
sng
Top