example of Data
--------------------
Col A Col B (By using CONCATENATE function)
A a,a,b,d
B d,d,c,a
C b,h,r

what i need in Col C is based on the criteria in Col A how many unique
entries are there in Col B, i.e.
if Col A = B then how may unique records are there in Col B, Answer is 3
(d,c,a)

what formula should i use for this,
NOTE : Col B each Concatenated entry is seperated by a ","

Where are you concatenating from?

I would think that it's easier to count unique entries in distinct cells
rather than in one

"Rajat" wrote:

example of Data
--------------------
Col A Col B (By using CONCATENATE function)
A a,a,b,d
B d,d,c,a
C b,h,r

what i need in Col C is based on the criteria in Col A how many unique
entries are there in Col B, i.e.
if Col A = B then how may unique records are there in Col B, Answer is 3
(d,c,a)

what formula should i use for this,
NOTE : Col B each Concatenated entry is seperated by a ","

concatenating from another 5 different sheets
i need to count unique entries from the Col - B

is there any way to do that,

reagrds
Rajat

If your data is in A1, try

=SUMPRODUCT(--(FREQUENCY(FIND(MID(A1,ROW(INDIRECT("1:"&LEN(A1))) *2-1,1),A1),FIND(MID(A1,ROW(INDIRECT("1:"&LEN(A1)))*2-1,1),A1))0))


This is based on your data being separated by a comma (without spaces).
This part of the formula pulls the 1,3, 5, 7, etc, characters

ROW(INDIRECT("1:"&LEN(A1)))*2-1

If your data contained no separators, this part of the formula changes to
ROW(INDIRECT("1:"&LEN(A1)))



"Rajat" wrote:

example of Data
--------------------
Col A Col B (By using CONCATENATE function)
A a,a,b,d
B d,d,c,a
C b,h,r

what i need in Col C is based on the criteria in Col A how many unique
entries are there in Col B, i.e.
if Col A = B then how may unique records are there in Col B, Answer is 3
(d,c,a)

what formula should i use for this,
NOTE : Col B each Concatenated entry is seperated by a ","

You have a solution provided the items in column B are all 1 character long,
if not your problem becomes more complicated and maybe should be handled by a
custom function.

--
Cheers,
Shane Devenshire


"Rajat" wrote:

example of Data
--------------------
Col A Col B (By using CONCATENATE function)
A a,a,b,d
B d,d,c,a
C b,h,r

what i need in Col C is based on the criteria in Col A how many unique
entries are there in Col B, i.e.
if Col A = B then how may unique records are there in Col B, Answer is 3
(d,c,a)

what formula should i use for this,
NOTE : Col B each Concatenated entry is seperated by a ","

Thanks, I forgot to add that caveat.


"ShaneDevenshire" wrote:

You have a solution provided the items in column B are all 1 character long,
if not your problem becomes more complicated and maybe should be handled by a
custom function.

--
Cheers,
Shane Devenshire


"Rajat" wrote:

example of Data
--------------------
Col A Col B (By using CONCATENATE function)
A a,a,b,d
B d,d,c,a
C b,h,r

what i need in Col C is based on the criteria in Col A how many unique
entries are there in Col B, i.e.
if Col A = B then how may unique records are there in Col B, Answer is 3
(d,c,a)

what formula should i use for this,
NOTE : Col B each Concatenated entry is seperated by a ","

Hi ShaneDevenshire & JBM

Thanks for your help. And wish you a Happy New Year.

As you said this formula works on 1 character long text,
But can you please gave me the formula if the concatenated text contain
multiple character including space (Rajat Roy,JB M,Shane Devenshire) each
entry will be seperated by a "," (comma)

thanks in advance,

Regards
Rajat

I think Harlan and Ron have a good solution. Looks like I've got more
homework to do (learning how to use Laurent's add-in functions).

I would certainly try the other suggestions first as the add-in offers a
number of other functions that look like they'd be handy.

If, by chance, you cannot use it (e.g. your workplace won't allow the add-in
to be installed), you could try a UDF. Post back if that's what you need and
I'll post one (if someone else has not done so).



"Rajat" wrote:

Hi ShaneDevenshire & JBM

Thanks for your help. And wish you a Happy New Year.

As you said this formula works on 1 character long text,
But can you please gave me the formula if the concatenated text contain
multiple character including space (Rajat Roy,JB M,Shane Devenshire) each
entry will be seperated by a "," (comma)

thanks in advance,

Regards
Rajat

On Sun, 31 Dec 2006 02:15:00 -0800, Rajat wrote:

example of Data
--------------------
Col A Col B (By using CONCATENATE function)
A a,a,b,d
B d,d,c,a
C b,h,r

what i need in Col C is based on the criteria in Col A how many unique
entries are there in Col B, i.e.
if Col A = B then how may unique records are there in Col B, Answer is 3
(d,c,a)

what formula should i use for this,
NOTE : Col B each Concatenated entry is seperated by a ","

This will work on any string sequences that are separated by ",".

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

Then use this array-formula. To enter an array formula, hold down
<ctrl<shift while hitting <enter. Excel will place braces {...} around the
formula:

=COUNTDIFF(REGEX.MID(A1,"[^,]+",ROW(
INDIRECT("$1:"&REGEX.COUNT(A1,"[^,]+")))))

If the strings might be longer than 255 characters, a VBA function can be used
to mimic the above.




--ron

Ron Rosenfeld wrote...
....
Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

Agreed.

Then use this array-formula. To enter an array formula, hold down
<ctrl<shift while hitting <enter. Excel will place braces {...} around the
formula:

=COUNTDIFF(REGEX.MID(A1,"[^,]+",ROW(
INDIRECT("$1:"&REGEX.COUNT(A1,"[^,]+")))))
....

Avoid volatile functions. One possibility would be using MOREFUNC's
INTVECTOR function rather than ROW(INDIRECT(...)), but for short
strings, I'd just use

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

On 2 Jan 2007 09:17:01 -0800, "Harlan Grove" wrote:

Avoid volatile functions.

Thanks for the tip, but could you explain why?


--ron

Ron Rosenfeld wrote...
On 2 Jan 2007 09:17:01 -0800, "Harlan Grove" wrote:
Avoid volatile functions.

Thanks for the tip, but could you explain why?

Because formulas calling volatile functions are recalculated every time
anything triggers any recalculation. For example, the nonvolatile
formula =SUM(A1:A4) recalcs only when cells in A1:A4 change, so Excel
won't recalc this formula when you enter something into cell X99 if
none of the cells in A1:A4 depend on X99. However, Excel will
recalculate the formula =SUM(INDIRECT("A1:A4")) whenever you enter
anything into any cell, even if A1:A4 were blank. Lots of formulas
calling volatile functions kills recalc speed.

I'd point to Charles Williams's DecisionModels site, but I'm having
problems accessing it.

On 2 Jan 2007 12:29:41 -0800, "Harlan Grove" wrote:

Because formulas calling volatile functions are recalculated every time
anything triggers any recalculation.

Thanks for the explanation.

I should read Williams stuff. http://www.decisionmodels.com/ should be the web
page but it seems to be inaccessible at this time.
--ron

Harlan,

I have found, by adding a messagebox to a UDF and making it volatile, that
it fires everytime *any* entry is made anywhere - even if it is not in the
active sheet. Can you tell me if the same also applies to Excel volatile
functions? I can't think of any way of checking that in an Excel function.

--
Regards,


Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk

Dear Harlan Grove

thanks a lot for your help and extremely sorry for the late response.

Regards

Rajat

On Tue, 02 Jan 2007 07:37:00 -0500, Ron Rosenfeld
wrote:

On Sun, 31 Dec 2006 02:15:00 -0800, Rajat wrote:

example of Data
--------------------
Col A Col B (By using CONCATENATE function)
A a,a,b,d
B d,d,c,a
C b,h,r

what i need in Col C is based on the criteria in Col A how many unique
entries are there in Col B, i.e.
if Col A = B then how may unique records are there in Col B, Answer is 3
(d,c,a)

what formula should i use for this,
NOTE : Col B each Concatenated entry is seperated by a ","

This will work on any string sequences that are separated by ",".

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

Then use this array-formula. To enter an array formula, hold down
<ctrl<shift while hitting <enter. Excel will place braces {...} around the
formula:

=COUNTDIFF(REGEX.MID(A1,"[^,]+",ROW(
INDIRECT("$1:"&REGEX.COUNT(A1,"[^,]+")))))

If the strings might be longer than 255 characters, a VBA function can be used
to mimic the above.




--ron


Based on Harlan's critique, the following formula would be more efficient:

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


--ron

Dear Ron Rosenfeld

thanks for the help and i'm extremely sorry for the late reply
The following formula provided by you worked well
=COUNTDIFF(REGEX.MID(A1,"[^,]+",INTVECTOR(REGEX.COUNT(A1,"[^,]+"),1)))

but i'm having problem when there is only 1 text the formula show the text
not the number i.e.

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

i need to count the unique text entry in the cell, can you please solve it,

Regards

Rajat

On Wed, 3 Jan 2007 06:42:00 -0800, Rajat wrote:

Dear Ron Rosenfeld

thanks for the help and i'm extremely sorry for the late reply
The following formula provided by you worked well
=COUNTDIFF(REGEX.MID(A1,"[^,]+",INTVECTOR(REGEX.COUNT(A1,"[^,]+"),1)))

but i'm having problem when there is only 1 text the formula show the text
not the number i.e.

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

i need to count the unique text entry in the cell, can you please solve it,

Regards

Rajat

That is an interesting issue. I will discuss it with Longre. The issue seems
to be that the single item is not being returned as an array-constant, so
COUNTDIFF apparently returns the item, and not the count.

However, a work around, which forces the single item to be returned as an
array, would be to use the ARRAY.JOIN function:

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

This seems to work as well as the previous.


--ron

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