Counting the common unique values between two comma-separated lists

[max333]

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.

[alansidman]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. No help to be offered until the link is provided.)

[max333]

Thanks. Here is the other link I cross posted. https://www.mrexcel.com/board/thread...lists.1140564/

[alansidman]

Are you still using XL2007 or a later version. If later, please update your profile as a solution may not be appropriate to your version. Some more advanced features are available in later versions.

[max333]

Thanks. I updated my profile. I use 2016 version.

[alansidman]

I am not understanding your expected results. Could you explain your logic and criteria that you used to obtain the expected results. If I count manually the unique values in your post #1, I get 7 unique values between the two rows. How do you arrive at 3?

[max333]

the first two rows:

12,12,13,14,16,17,20
11,13,17,18,18,20

the result is 3 because 13,17,20 are in both rows.

I only need the common / shared unique values in both cells.

for example:

11,14,16,17,18,19,19,20
12,12,13,16,17,19,20

here result is : 4 ( 16,17,19,20) even though there are two 19s in the first list, it is counted once.

[max333]

so if there was a situation like this:

15,15,19
15,15,20

The result would be 1. because one unique number is present in both lists. (15).

I hope this makes it clearer. if not you can ask me.

[Bo_Ry]

Please try at C2
for Fixed 2 digits number

=COUNT(MATCH(IF(FREQUENCY(--MID(A2,ROW(A$1:INDEX(A:A,LEN(A2)-LEN(SUBSTITUTE(A2,",",))+1))*3-2,2),ROW(A$1:A$99)),ROW(A$1:A$99)),--MID(A3,ROW(A$1:A$20)*3-2,2),))

or 1-3 digits

=COUNT(MATCH(IF(FREQUENCY(FILTERXML("<b><a>"&SUBSTITUTE(A2,",","</a><a>")&"</a></b>","//a"),ROW($A$1:$A$999)),ROW($A$1:$A$999)),FILTERXML("<b><a>"&SUBSTITUTE(A3,",","</a><a>")&"</a></b>","//a"),))


Or UDF =CommonU(A2)

Please Login or Register  to view this content.

or Power Query

Please Login or Register  to view this content.