First apologies for new thread.
Second: yes i have a unique list. In column A is my unique list and in
column B (different length and width) is the list of entries I need counted.
SOme cells have multiple values from column A. So I need a final column C I
guess that shows the number of times an entry occurs fromthe list in column A
in column B.

I am not proficient enough to understnad your example. Sorry! I can use
MSN chat though! Pay!!! LOL....

"Dave Peterson" wrote:

I think you're gonna have to bite the bullet and create a list of unique
names--one name per cell. (Maybe you already have that list???)

Then with that list in Sheet2 (A1:Axx), you could use a formula like this in B1:

=SUMPRODUCT(
(LEN(A1:A10)-LEN(SUBSTITUTE(UPPER(sheet1!$A$1:$A$10),UPPER(a1), "")))/LEN(a1))

And drag down as far as you need.




Daniel_ITSM wrote:

Hi Dave,

How can I get a report to look like the below without having to explicitly
define each cell or name? My column is 5800 in length and varies in number
of entries, each separated by comma.

Unique Names Names used
Daniel Daniel, Celia
Celia Daniel
Sherlock Batman,Sherlock
Batman Daniel
Celia

Report then would be:
Daniel 3
Celia 2
Batman 1
Sherlock 1

"Dave Peterson" wrote:

You could use a formula like:

=SUMPRODUCT(
(LEN(A1:A10)-LEN(SUBSTITUTE(UPPER(A1:A10),UPPER("PROF_CARER_EPI SODES"),"")))
/LEN("PROF_CARER_EPISODES"))

(all one cell)

Adjust the range to match--but you can't use the whole column until xl2007.

Daniel_ITSM wrote:

I am not proficient with Excel. My challenge is to find out how many times a
term is used in a very long column.
PROF_CARER_EPISODES
PROF_CARER_EPISODES, PROVIDER_SPELLS
CASENOTES
PROVIDER_SPELLS

SYSTEM_PROFILES

LETTER_CONFIGURATIONS

SERVICE_POINT_STAYS
LDD_LOCAL_DATAVALUES
WAITING_LIST_HISTORIES
PROF_CARER_EPISODES
PROF_CARER_EPISODES
PROF_CARER_EPISODES, SERVICE_POINT_STAYS

LDD_LOCAL_DATAVALUES
LDD_LOCAL_DATAVALUES
DIAGNOSIS_PROCEDURES

ADDRESS_ROLES
ADDRESS_ROLES

PROF_CARER_EPISODES
PATIENTS
PROVIDER_SPELLS, SERVICE_POINT_STAYS

Since some cells have more than one value, how can I break them out to count
how many occurences there are?

--

Dave Peterson


--

Dave Peterson