counting problem

[Guest]

hi all,
On the range "pName", how to count it's elements on basis that entries like
wood.1,wood., wood.3 will be counted as only 1 i.e for "wood" and not 3 as
unique.
(Note: the range "pName" include blanks at the end of the list not between
items)
thanks for any help

 

[mzehr]

Hi,
Without having more information, it sounds like you might
need to insert a helper column with the following formula,
assuming your data is in column a =IF(ISERROR(LEFT(A1,FIND
(".",A1)-1)),A1,LEFT(A1,FIND(".",A1)-1))
That will strip out all the items to the right of the
decimal point, assuming that there is one. Then you could
easily use a pivot table to summarize your data

for more information check out Chip Pearson's website:
http://www.cpearson.com/excel/duplicat.htm#CountingUnique

 

[RagDyeR]

Try this:

=COUNTIF(pName,"wood*")
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


hi all,
On the range "pName", how to count it's elements on basis that entries like
wood.1,wood., wood.3 will be counted as only 1 i.e for "wood" and not 3 as
unique.
(Note: the range "pName" include blanks at the end of the list not between
items)
thanks for any help

 

[Guest]

thanks mzehr,
for your help
but how to avoid having that redundant helper column and still manage to
goal of finding the true number of items?

 

[mzehr]

See RagDyeR's post. When you are pulling partial
information from a cell, you will have to either strip it
out using a helper column like I suggested, or use a count
summary like RagDyeR listed. By stripping it out you will
also be able to use pivot tables to further summarize your
data as well. Good luck.