Counting unique items, disregarding thier annexs

[excelFan]

"THIS IS A rePOST WITH SOME CORRECTION"
hi all,
seeking help please.
The column A1:A100 contains items and also empty cells like
facil.1,beauty.2,facil.,facil.3,beauty.4 and so on....

Looking for a solution where on counting items will yield 2 only (for
"facil"
and "beauty") and not 5 as each entry is unique, as the flowing formula do:
=sum(if(frequency(match(A1:A100,A1:A100,0)match(A1:A100,A1:A100,0))>0,1))
many thanks for any help.

 

[Ron Rosenfeld]

"THIS IS A rePOST WITH SOME CORRECTION"
hi all,
seeking help please.
The column A1:A100 contains items and also empty cells like
facil.1,beauty.2,facil.,facil.3,beauty.4 and so on....

Looking for a solution where on counting items will yield 2 only (for
"facil"
and "beauty") and not 5 as each entry is unique, as the flowing formula do:
=sum(if(frequency(match(A1:A100,A1:A100,0)match(A1:A100,A1:A100,0))>0,1))
many thanks for any help.

Try this *array* formula:

=SUM(IF(FREQUENCY(MATCH(LEFT(A1:A5,FIND(".",A1:A5)-1),
LEFT(A1:A5,FIND(".",A1:A5)-1),0),MATCH(LEFT(A1:A5,FIND(
".",A1:A5)-1),LEFT(A1:A5,FIND(".",A1:A5)-1),0))>0,1))

To enter an *array* formula, after typing or pasting it into the cell, hold
down <ctrl><shift> while hitting <enter>. Excel will place braces {...} around
the formula.


--ron

 

[Ron Rosenfeld]

thanks Ron for your reply,
i made a try with your proposal but sorry to tell you; it's not working
maybe due to empty cells in the range A1:A100
many thanks Ron.
still waiting for solution

Did your original formula work over that range with the empty cells?


--ron

 

[Ron Rosenfeld]

thanks Ron for your reply,
i made a try with your proposal but sorry to tell you; it's not working
maybe due to empty cells in the range A1:A100
many thanks Ron.
still waiting for solution.

OK, well assuming now that the contents of A1:a100 are either BLANK or have a
string which includes a ".", then the *array* formula:

=SUM(IF(FREQUENCY(MATCH(IF(A1:A100<>"",LEFT(A1:A100,
FIND(".",A1:A100)-1)),IF(A1:A100<>"",LEFT(A1:A100,
FIND(".",A1:A100)-1)),0),MATCH(IF(A1:A100<>"",LEFT(A1:A100,
FIND(".",A1:A100)-1)),IF(A1:A100<>"",LEFT(A1:A100,FIND(".",A1:A100)-1)),0))>0,1))

should take care of the blanks.

========================

A simpler solution might be to download morefunc.xll -- a free download written
by Laurent Longre http://longre.free.fr/english/ and use his COUNTDIFF
function.

Again it would be an array formula:

=COUNTDIFF(LEFT(IF(A1:A100<>"",A1:A100),FIND(".",IF(A1:A100<>"",A1:A100))-1))


--ron

 

[goose]

-----Original Message-----


Did your original formula work over that range with the empty cells?


--ron
.
I know this sounds simple but have you tried COUNT IF

If a1:a100 contain the word Beauty and that is what you
want to count then You would enter Countif
(a1:a100,"Beauty") it ignores blanks and anything that
isn't the word beauty.

--Goose

 

[Ron Rosenfeld]

I know this sounds simple but have you tried COUNT IF
If a1:a100 contain the word Beauty and that is what you
want to count then You would enter Countif
(a1:a100,"Beauty") it ignores blanks and anything that
isn't the word beauty.

That would give the number of "beauty"'s, but not the number of unique entries.


--ron

 

[excelFan]

hi Ron,
many thanks again, also i tried your second suggestion where you said
this shoud take care of blanks ,but did not work agreeably
Let's keep trying to find an answer.
Good luck and still waiting.

 

[Ron Rosenfeld]

hi Ron,
many thanks again, also i tried your second suggestion where you said
this shoud take care of blanks ,but did not work agreeably
Let's keep trying to find an answer.
Good luck and still waiting.

There's no trouble shooting I can do based on "did not work agreeably".


--ron