Counting Unique entry from Concatenated list

[Harlan Grove]

Ron Rosenfeld wrote...
....

To correct that problem, we will exclude the #VALUE! error from being counted:

=COUNTDIFF(ARRAY.JOIN(REGEX.MID(A1,"[^,]+",
INTVECTOR(REGEX.COUNT(A1,"[^,]+"),1))),,#VALUE!)

....

An alternative would be to prevent the error. One option, which
excludes empty fields and returns 0 for blank cells or cells evaluating
to "",

=COUNTDIFF(EVAL("{"""&SUBSTITUTE(A1,",",""",""")&"""}"),,"")

And an alternative regex formula would be

=COUNTDIFF(REGEX.MID(A1&",","[^,]+",INTVECTOR(REGEX.COUNT(A1&",,",","),1)),,"")

But maybe the direct approach makes more sense.

=IF(WORDCOUNT(A1,","),COUNTDIFF(REGEX.MID(A1,"[^,]+",
INTVECTOR(WORDCOUNT(A1,","),1))),0)

 

[Harlan Grove]

Harlan Grove wrote...
....

But maybe the direct approach makes more sense.

=IF(WORDCOUNT(A1,","),COUNTDIFF(REGEX.MID(A1,"[^,]+",
INTVECTOR(WORDCOUNT(A1,","),1))),0)

Failed to test that with a single field in A1. Make that

=IF(WORDCOUNT(A1,",")<2,WORDCOUNT(A1,","),
COUNTDIFF(REGEX.MID(A1,"[^,]+",INTVECTOR(WORDCOUNT(A1,","),1))))

 

[Ron Rosenfeld]

Ron Rosenfeld wrote...
...

To correct that problem, we will exclude the #VALUE! error from being counted:

=COUNTDIFF(ARRAY.JOIN(REGEX.MID(A1,"[^,]+",
INTVECTOR(REGEX.COUNT(A1,"[^,]+"),1))),,#VALUE!)

...

An alternative would be to prevent the error. One option, which
excludes empty fields and returns 0 for blank cells or cells evaluating
to "",

=COUNTDIFF(EVAL("{"""&SUBSTITUTE(A1,",",""",""")&"""}"),,"")

And an alternative regex formula would be

=COUNTDIFF(REGEX.MID(A1&",","[^,]+",INTVECTOR(REGEX.COUNT(A1&",,",","),1)),,"")

But maybe the direct approach makes more sense.

=IF(WORDCOUNT(A1,","),COUNTDIFF(REGEX.MID(A1,"[^,]+",
INTVECTOR(WORDCOUNT(A1,","),1))),0)

All work. I'd still like to see COUNTDIFF return a 1 with a single element not
enclosed in an array constant.


--ron

 

[Guest]

Now its not showing on my sheet, After i added the ARRAY.JOIN part of the
formulae this message box was shown. Thanx for your help once again.

Regards
Rajat

 

[Guest]

Dear Harlan and Ron

Thanks a lot for providing the formulae, both the formula worked well
without any problem.

Regards

Rajat