count text occurences in a column

D

Daniel_ITSM

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?
 
G

Gary''s Student

Consider using a pivot table. It can take your posted data and make:

ADDRESS_ROLES 2
CASENOTES 1
DIAGNOSIS_PROCEDURES 1
LDD_LOCAL_DATAVALUES 3
LETTER_CONFIGURATIONS 1
PATIENTS 1
PROF_CARER_EPISODES 4
PROF_CARER_EPISODES, PROVIDER_SPELLS 1
PROF_CARER_EPISODES, SERVICE_POINT_STAYS 1
PROVIDER_SPELLS 1
PROVIDER_SPELLS, SERVICE_POINT_STAYS 1
SERVICE_POINT_STAYS 1
SYSTEM_PROFILES 1
WAITING_LIST_HISTORIES 1
Grand Total 20


listing each item of text and how many times it occurs. See:

http://peltiertech.com/Excel/Pivots/pivotstart.htm
 
R

Ron Coderre

Try something like this:

With
Col_A containing various text entries or blanks

This formula returns the count cells that contain the text string:
"PROVIDER_SPELLS"
=COUNTIF(A:A,"*PROVIDER_SPELLS*")

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP
 
D

Dave Peterson

You could use a formula like:

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

(all one cell)

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

Daniel_ITSM

Hi Gary,

The answers need to be unique.
Since some of the cells have two entires separated by commas, I need to
count each item.
Pivot won't do it.
 
D

Daniel_ITSM

Hi Ron,

I do have another coumn that shows the unique entries,but then how do I get
a count of Provider Speels without having to enter a hundred formulas?

Thanks!
 
R

Ron Coderre

If you already have a list of the unique values, an amended version of the
formula I posted would return the count of cells that contain that value.

Example:
E2: PROVIDER_SPELLS
The count of cells containing that value
F2: =COUNTIF(A:A,"*"&E2&"*")

E3: PROF_CARER_EPISODES
The count of cells containing that value
F3: =COUNTIF(A:A,"*"&E3&"*")

If you need something else, though....perhaps you could give a small example
of the source data and the structure of the final table.

***********
Regards,
Ron

XL2002, WinXP
 
D

Daniel_ITSM

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
 
D

Daniel_ITSM

Hi Dave,
I do have another column that shows the unique entries,but then how do I get
a count of Provider Spells without having to enter a hundred formulas?

I need to know the cont of all of the items,not just one in particluar.

Thanks!
 
D

Daniel_ITSM

Hi Ron,

Look at this then and tell me how I could get the report desired without
explictly naming each cell reference or actual name? My column 5800 cells
with multiple and single entries.

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
 
R

Ron Coderre

With
A1:A6 containing this list
NamesUsed
Daniel, Celia
Daniel
Batman, Sherlock
Daniel
Celia

AND....
E1:F5 containing this list
UniqueNames Counts
Daniel
Celia
Batman
Sherlock


THEN.....
These formula return the desired counts:
F2: =COUNTIF(A:A,"*"&E2&"*")
Copy that formula down through F5

This is the result set:
UniqueNames Counts
Daniel 3
Celia 2
Batman 1
Sherlock 1


Now....if you wanted those value in a report, perhaps you could employ a
Pivot Table with UniqueNames as the ROW value and Sum of Counts in the DATA
section. That way you could filter out names with zero counts, etc.

(Post back if you have more questions)

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
D

Daniel_ITSM

WellI enter the formula and begin to copy but they are all the same with no
increment. If all I do is just enter it into one cell I get nothing but the
formula in my cell. GRRRRR!!!! Do you use chat? Can I pay you?
 
R

Ron Coderre

I see by your NEW THREAD that you'd like the UniqueNames list populated
automatically from the values in the source table. While it could
conceivably be done with formulas, a VBA solution would consume a lot less
computational overhead, since the procedure would only be run on-demand. If
that is something you're interested in, let us know. Somebody may have
already created the code and would only need to re-post it , or the link, for
you.

***********
Regards,
Ron

XL2002, WinXP
 
R

Ron Coderre

Please don't offer money....these are free forums. We do this because we
enjoy it.
Also, IM'ing isn't in the best interest of the forum, because nobody else
will be able to see the solution process.

Can you post the formulas you've tried? I suspect we'll then be able to
spot what's going wrong.

***********
Regards,
Ron

XL2002, WinXP
 
D

Daniel_ITSM

I dont think I need another unique name list.
What I need is a count like you did!
 
D

Dave Peterson

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.
 
R

Ron Coderre

Thanks for the clarification.....I withdraw the comment. <g>

***********
Regards,
Ron

XL2002, WinXP
 
R

Ron Coderre

Hmmmm....you say you only see the formula and not the results?

Maybe one of these is the problem....

1)Excel is set to display formulas instead of their results.

If that's true...try this...
Hold down the [ctrl] key and press ~
(the tilde is just under the [ESC] key)
That will toggle formula display on/off

OR
2)The cells with the formula have a number format of "Text"
If that's true...try this...
From the Excel main menu
<format><cells><number tab>
Category: (should be almost anything but "text"...try General)

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
D

Daniel_ITSM

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....
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

'Data Type Mismatch In Criteria Expression' error when using 'Count' in query Design; Help! 0
Counting text which meets a criteria 2
Need to count no. of rows where cell in column A = "value1" AND cellin column B (same row) = "value2 2
Counting and deleting 7
SUMPRODUCT formula I think w/ wild card & number vs number as text 8
Counting Individual Companies 6
Function in Excel to count and list number of entries of text valuesin a row. 2
Counting Alpha entries 2

Top