|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
March 7th, 2007, 05:12 PM
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
March 7th, 2007, 05:33 PM
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
March 7th, 2007, 05:35 PM
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?
|
|
#4
March 7th, 2007, 05:36 PM
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
|
|
#5
March 7th, 2007, 05:37 PM
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?
|
|
#6
March 7th, 2007, 05:38 PM
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?
|
|
#7
March 7th, 2007, 06:01 PM
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?
|
|
#8
March 7th, 2007, 06:02 PM
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
|
|
#9
March 7th, 2007, 06:02 PM
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
|
|
#10
March 7th, 2007, 06:03 PM
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?
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
