|
| 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 |
|
#11
March 7th, 2007, 06:31 PM
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? 
|
|
#12
March 7th, 2007, 06:35 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
March 7th, 2007, 06:41 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
March 7th, 2007, 06:44 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
March 7th, 2007, 06:52 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
March 7th, 2007, 06:53 PM
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
|
|
#17
March 7th, 2007, 06:53 PM
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
|
|
#18
March 7th, 2007, 06:54 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
March 7th, 2007, 07:01 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 formatcellsnumber 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
March 7th, 2007, 07:09 PM
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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
