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