| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I count the # of unique occurences of a text in a column? | Excel Worksheet Functions | |||
| Count # of Occurences | Excel Discussion (Misc queries) | |||
| Count number of occurences in 1 column only if something in anothe | Excel Worksheet Functions | |||
| Count occurences between dates | Excel Worksheet Functions | |||
| Count occurences between dates | Excel Worksheet Functions | |||
Hybrid Mode
