CODEWITHSUNDEEP
excelexcel-formuladistinct-values
kyrpav
kyrpav

Reputation: 778

Distinct count Comma separated values in column excel

How can i distinct count values in a column where inside each row cell i could have more than one values separated with comma like this table:

I have a solution if a split the table but can i use one function that does this on the fly? so no temp tables etc...?

enter image description here

Upvotes: 1

Views: 1469

Answers (1)

bosco_yip
bosco_yip

Reputation: 3802

Assume input data housed in A1:A14 with header

enter image description here

1] For Distinct language list

In B2, formula copied down :

=IFERROR(FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(",",1,A$2:A$14),",","</b><b>")&"</b></a>","//b[not(preceding::*=.)]["&ROW(A1)&"]"),"")

Edit :

2] For distinct count

In B2 :

=SUMPRODUCT(0+ISTEXT(FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(",",1,A$2:A$14),",","</b><b>")&"</b></a>","//b[not(preceding::*=.)]")))

enter image description here

The formula using TEXTJOIN function which is available in Office365 or Excel2019, and FILTERXML function which is available in Excel2013

Upvotes: 5

Related Questions