Counting Unique entry from Concatenated list

Dear Ron Rosenfeld

I've a problem in this formula also, following formula return value 1 when
cell is blank cell A1 value nil as a result of CONCATENATE function i used in
cell A1

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

When Cell A Contain - Formula Result
Roy,Roy,b,c - 3
Roy,,, - 1
,,,, - 1

in Cell A i used formula =CONCATENATE(Z1,Y1,X1,K1)
Is there any other work around?

Regards

Rajat

another point i forgot to mention after entering the last formula in the cell
a Msg Box appear which is as follows -

Title : Microsofy Visual Basic
Body Message : User-defined type not defined
Button : Ok , Help

is the formula problem is due to this or any other matter? Hope that you can
solve it.

regards

Rajat

On Wed, 3 Jan 2007 12:28:02 -0800, Rajat wrote:

Dear Ron Rosenfeld

I've a problem in this formula also, following formula return value 1 when
cell is blank cell A1 value nil as a result of CONCATENATE function i used in
cell A1

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

When Cell A Contain - Formula Result
Roy,Roy,b,c - 3
Roy,,, - 1
,,,, - 1

in Cell A i used formula =CONCATENATE(Z1,Y1,X1,K1)
Is there any other work around?

Regards

Rajat

What is happening:

When the REGEX returns nothing, as it will if A1 is empty, then the ARRAY.JOIN
returns a #VALUE! error. COUNTDIFF then counts that as one unique entry.

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!)

The formula will now return a 0.


--ron

On Wed, 3 Jan 2007 12:35:03 -0800, Rajat wrote:

another point i forgot to mention after entering the last formula in the cell
a Msg Box appear which is as follows -

Title : Microsofy Visual Basic
Body Message : User-defined type not defined
Button : Ok , Help

is the formula problem is due to this or any other matter? Hope that you can
solve it.

regards

Rajat

I cannot reproduce this error message. Perhaps more detail?


--ron

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 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))))

On 3 Jan 2007 15:03:28 -0800, "Harlan Grove" wrote:

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

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

"Ron Rosenfeld" wrote:

On Wed, 3 Jan 2007 12:35:03 -0800, Rajat wrote:

another point i forgot to mention after entering the last formula in the cell
a Msg Box appear which is as follows -

Title : Microsofy Visual Basic
Body Message : User-defined type not defined
Button : Ok , Help

is the formula problem is due to this or any other matter? Hope that you can
solve it.

regards

Rajat

I cannot reproduce this error message. Perhaps more detail?


--ron

Dear Harlan and Ron

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

Regards

Rajat