Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default count text occurences in a column

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?
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,058
Default count text occurences in a column

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
--
Gary''s Student
gsnu200709


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

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 698
Default count text occurences in a column

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


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

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default count text occurences in a column

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
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 14
Default count text occurences in a column

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.

"Gary''s Student" wrote:

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
--
Gary''s Student
gsnu200709


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



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 14
Default count text occurences in a column

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!

"Ron Coderre" wrote:

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


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

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 698
Default count text occurences in a column


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


"Daniel_ITSM" wrote:

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!

"Ron Coderre" wrote:

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


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

  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 14
Default count text occurences in a column

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

  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 14
Default count text occurences in a column

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!


"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

  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 14
Default count text occurences in a column

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


"Ron Coderre" wrote:


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


"Daniel_ITSM" wrote:

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!

"Ron Coderre" wrote:

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


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



  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 698
Default count text occurences in a column

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


"Daniel_ITSM" wrote:

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


"Ron Coderre" wrote:


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


"Daniel_ITSM" wrote:

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!

"Ron Coderre" wrote:

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


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

  #12   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 14
Default count text occurences in a column

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?

"Ron Coderre" wrote:

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


"Daniel_ITSM" wrote:

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


"Ron Coderre" wrote:


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


"Daniel_ITSM" wrote:

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!

"Ron Coderre" wrote:

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


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

  #13   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 698
Default count text occurences in a column

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


"Ron Coderre" wrote:

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


"Daniel_ITSM" wrote:

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


"Ron Coderre" wrote:


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


"Daniel_ITSM" wrote:

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!

"Ron Coderre" wrote:

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


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

  #14   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 698
Default count text occurences in a column

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


"Daniel_ITSM" wrote:

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?

"Ron Coderre" wrote:

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


"Daniel_ITSM" wrote:

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


"Ron Coderre" wrote:


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


"Daniel_ITSM" wrote:

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!

"Ron Coderre" wrote:

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


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

  #15   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 14
Default count text occurences in a column

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

"Ron Coderre" wrote:

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


"Ron Coderre" wrote:

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


"Daniel_ITSM" wrote:

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


"Ron Coderre" wrote:


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


"Daniel_ITSM" wrote:

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!

"Ron Coderre" wrote:

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


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



  #16   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default count text occurences in a column

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
  #17   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default count text occurences in a column

You do have the list!

Check that other followup.

Daniel_ITSM wrote:

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!

"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
  #18   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 698
Default count text occurences in a column

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

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

XL2002, WinXP


"Daniel_ITSM" wrote:

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

"Ron Coderre" wrote:

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


"Ron Coderre" wrote:

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


"Daniel_ITSM" wrote:

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


"Ron Coderre" wrote:


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


"Daniel_ITSM" wrote:

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!

"Ron Coderre" wrote:

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


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

  #19   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 698
Default count text occurences in a column

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


"Daniel_ITSM" wrote:

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?

"Ron Coderre" wrote:

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


"Daniel_ITSM" wrote:

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


"Ron Coderre" wrote:


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


"Daniel_ITSM" wrote:

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!

"Ron Coderre" wrote:

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


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

  #20   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 14
Default count text occurences in a column

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



  #21   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default count text occurences in a column

Insert a new column A--to hold the count. Now everything has shifted over one
column.

Put this in A1:

=SUMPRODUCT(
(LEN(C1:C10)-LEN(SUBSTITUTE(UPPER($c$1:$c$10),UPPER(b1),"")))/LEN(b1))


Adjust the range to match what's in column C.

And drag down column A until you're at the bottom (even with column B).



Daniel_ITSM wrote:

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


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I count the # of unique occurences of a text in a column? Rob Kaiser Excel Worksheet Functions 10 November 21st 07 09:16 PM
Count # of Occurences Sweepea Excel Discussion (Misc queries) 6 December 14th 06 02:27 AM
Count number of occurences in 1 column only if something in anothe Wenster Excel Worksheet Functions 2 February 7th 05 09:58 PM
Count occurences between dates DJ Dusty Excel Worksheet Functions 0 November 11th 04 09:02 PM
Count occurences between dates DJ Dusty Excel Worksheet Functions 3 November 11th 04 12:25 AM


All times are GMT +1. The time now is 01:49 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"