Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting # of Times an Item Shows Up in a Table
Hi,
Is there a way to select a table and have excel produce a list ranking the # of times each item shows up in the list, i.e. dog 5 cat 2 bird 1 ??? Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting # of Times an Item Shows Up in a Table
Have you tried the COUNTIF function?
"Ralph" wrote in message ... Hi, Is there a way to select a table and have excel produce a list ranking the # of times each item shows up in the list, i.e. dog 5 cat 2 bird 1 ??? Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting # of Times an Item Shows Up in a Table
Well, with that function I'd have to specify WHAT I want it to count such as
a function for dog, one for cat, etc... I just want excel to compile a list of everything that was in the table with the # of times it appears in the table without having to write a function for each entry, you understand? have a suggestion? THANKS! "Barb Reinhardt" wrote: Have you tried the COUNTIF function? "Ralph" wrote in message ... Hi, Is there a way to select a table and have excel produce a list ranking the # of times each item shows up in the list, i.e. dog 5 cat 2 bird 1 ??? Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting # of Times an Item Shows Up in a Table
Maybe not exactly what you're looking for, but ...
You could enter this formula in the column adjacent to the datalist, which will simply display the count of every item in the table: =COUNTIF($A$1:$A$100,A1) And copy down. Then, this formula will give you a count of the unique items in the table: =SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&"")) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Ralph" wrote in message ... Hi, Is there a way to select a table and have excel produce a list ranking the # of times each item shows up in the list, i.e. dog 5 cat 2 bird 1 ??? Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting # of Times an Item Shows Up in a Table
B1: = A1
B2: =IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""), 0)),"", INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH( 0,COUNTIF(B$1:B1,$A$1:$A$2 0&""),0))) as an array formula, commit with Ctrl-Shift-Enter, and copy down C1: = COUNTIF(A:A,B1) and copy down -- HTH RP (remove nothere from the email address if mailing direct) "Ralph" wrote in message ... Hi, Is there a way to select a table and have excel produce a list ranking the # of times each item shows up in the list, i.e. dog 5 cat 2 bird 1 ??? Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting # of Times an Item Shows Up in a Table
i cant seem to get these formulas to work, but i'm very excited that i think
there may be a solution here, is it possible that i could email the file to you and you could show me how to arrange those formulas??? or can I post the file somehow to this discussion group?? "RagDyeR" wrote: Maybe not exactly what you're looking for, but ... You could enter this formula in the column adjacent to the datalist, which will simply display the count of every item in the table: =COUNTIF($A$1:$A$100,A1) And copy down. Then, this formula will give you a count of the unique items in the table: =SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&"")) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Ralph" wrote in message ... Hi, Is there a way to select a table and have excel produce a list ranking the # of times each item shows up in the list, i.e. dog 5 cat 2 bird 1 ??? Thanks |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting # of Times an Item Shows Up in a Table
How about a Pivot Table? Select your data, then Data Pivot Table. When
you get to the layout, drag the column header for items into the Rows field AND into the data field. Check the label on the item in the data field; if is isn't 'Count of xxxx', double-click it and change the 'summarize' function to Count. Caveat regarding Pivot Tables: if your data changes, the Pivot won't refresh automatically; right-click the table and select Refresh Data. "Ralph" wrote: Hi, Is there a way to select a table and have excel produce a list ranking the # of times each item shows up in the list, i.e. dog 5 cat 2 bird 1 ??? Thanks |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting # of Times an Item Shows Up in a Table
I know you posted this in the Worksheet Functions forum, but I think a Pivot
Table might give you what you're looking for more easily: Select your column of data (Make sure it has a col heading, I'll assume "Type") DataPivot Table Use: Excel List Range: If not already selected, select your data Click the [Layout] button -ROW: Type -DATA: Count of Type Select a destination for the Pivot Table Click [OK] Next, on the Pivot Table, double-click on the Type heading to view the settings dialog. Click the [dvance]button Set Autosort: Descending Using field: Count of Type Click [OK] That will list each unique item in the list in descending order of its count. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Ralph" wrote: Hi, Is there a way to select a table and have excel produce a list ranking the # of times each item shows up in the list, i.e. dog 5 cat 2 bird 1 ??? Thanks |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting # of Times an Item Shows Up in a Table
I am having the same difficulty as with RagDyeR's post which is exactly where
to put these formulas. My table is from A1 to G11 so where do I put those formulas Bob? Sorry, I'm a little Excel challenged. The easiest way to communicate all this is if I could somehow post the file to you. Is that possible? Thanks "Bob Phillips" wrote: B1: = A1 B2: =IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""), 0)),"", INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH( 0,COUNTIF(B$1:B1,$A$1:$A$2 0&""),0))) as an array formula, commit with Ctrl-Shift-Enter, and copy down C1: = COUNTIF(A:A,B1) and copy down -- HTH RP (remove nothere from the email address if mailing direct) "Ralph" wrote in message ... Hi, Is there a way to select a table and have excel produce a list ranking the # of times each item shows up in the list, i.e. dog 5 cat 2 bird 1 ??? Thanks |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting # of Times an Item Shows Up in a Table
ahhhhhh!!! Thanks Ron & Bpeltzer but you are over my head with this, maybe
I'm too excel challenged but I'm finding your instructions a little too complex for me to pull this off. An additional complexity is that in the fields of the table I have items separated by commas, such as in field b2, the entry is "cat, dog, bird", in field c3 the entry is "cat, bird", is this going to screw up your ideas in terms of counting everything?? I wish I could post the file somehow, it would make all this MUCH easier,........ "Ron Coderre" wrote: I know you posted this in the Worksheet Functions forum, but I think a Pivot Table might give you what you're looking for more easily: Select your column of data (Make sure it has a col heading, I'll assume "Type") DataPivot Table Use: Excel List Range: If not already selected, select your data Click the [Layout] button -ROW: Type -DATA: Count of Type Select a destination for the Pivot Table Click [OK] Next, on the Pivot Table, double-click on the Type heading to view the settings dialog. Click the [dvance]button Set Autosort: Descending Using field: Count of Type Click [OK] That will list each unique item in the list in descending order of its count. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Ralph" wrote: Hi, Is there a way to select a table and have excel produce a list ranking the # of times each item shows up in the list, i.e. dog 5 cat 2 bird 1 ??? Thanks |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting # of Times an Item Shows Up in a Table
ahhhhhh!!! Thanks Ron & Bpeltzer but you are over my head with this, maybe
I'm too excel challenged but I'm finding your instructions a little too complex for me to pull this off. An additional complexity is that in the fields of the table I have items separated by commas, such as in field b2, the entry is "cat, dog, bird", in field c3 the entry is "cat, bird", is this going to screw up your ideas in terms of counting everything?? I wish I could post the file somehow, it would make all this MUCH easier,........ "Ron Coderre" wrote: I know you posted this in the Worksheet Functions forum, but I think a Pivot Table might give you what you're looking for more easily: Select your column of data (Make sure it has a col heading, I'll assume "Type") DataPivot Table Use: Excel List Range: If not already selected, select your data Click the [Layout] button -ROW: Type -DATA: Count of Type Select a destination for the Pivot Table Click [OK] Next, on the Pivot Table, double-click on the Type heading to view the settings dialog. Click the [dvance]button Set Autosort: Descending Using field: Count of Type Click [OK] That will list each unique item in the list in descending order of its count. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Ralph" wrote: Hi, Is there a way to select a table and have excel produce a list ranking the # of times each item shows up in the list, i.e. dog 5 cat 2 bird 1 ??? Thanks |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting # of Times an Item Shows Up in a Table
field b2, the entry is "cat, dog, bird", in field c3 the entry is "cat,
bird"<< Hmmm....that would have been a really nice piece of information to know right up front.. :\ The complexity of your solution (if there is one) just increased significantly. We'll see what we can do for you. *********** Regards, Ron XL2002, WinXP-Pro "Ralph" wrote: ahhhhhh!!! Thanks Ron & Bpeltzer but you are over my head with this, maybe I'm too excel challenged but I'm finding your instructions a little too complex for me to pull this off. An additional complexity is that in the fields of the table I have items separated by commas, such as in field b2, the entry is "cat, dog, bird", in field c3 the entry is "cat, bird", is this going to screw up your ideas in terms of counting everything?? I wish I could post the file somehow, it would make all this MUCH easier,........ "Ron Coderre" wrote: I know you posted this in the Worksheet Functions forum, but I think a Pivot Table might give you what you're looking for more easily: Select your column of data (Make sure it has a col heading, I'll assume "Type") DataPivot Table Use: Excel List Range: If not already selected, select your data Click the [Layout] button -ROW: Type -DATA: Count of Type Select a destination for the Pivot Table Click [OK] Next, on the Pivot Table, double-click on the Type heading to view the settings dialog. Click the [dvance]button Set Autosort: Descending Using field: Count of Type Click [OK] That will list each unique item in the list in descending order of its count. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Ralph" wrote: Hi, Is there a way to select a table and have excel produce a list ranking the # of times each item shows up in the list, i.e. dog 5 cat 2 bird 1 ??? Thanks |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting # of Times an Item Shows Up in a Table
yikes, Sorry Ron but I really really appreciate your help :)
"Ron Coderre" wrote: field b2, the entry is "cat, dog, bird", in field c3 the entry is "cat, bird"<< Hmmm....that would have been a really nice piece of information to know right up front.. :\ The complexity of your solution (if there is one) just increased significantly. We'll see what we can do for you. *********** Regards, Ron XL2002, WinXP-Pro "Ralph" wrote: ahhhhhh!!! Thanks Ron & Bpeltzer but you are over my head with this, maybe I'm too excel challenged but I'm finding your instructions a little too complex for me to pull this off. An additional complexity is that in the fields of the table I have items separated by commas, such as in field b2, the entry is "cat, dog, bird", in field c3 the entry is "cat, bird", is this going to screw up your ideas in terms of counting everything?? I wish I could post the file somehow, it would make all this MUCH easier,........ "Ron Coderre" wrote: I know you posted this in the Worksheet Functions forum, but I think a Pivot Table might give you what you're looking for more easily: Select your column of data (Make sure it has a col heading, I'll assume "Type") DataPivot Table Use: Excel List Range: If not already selected, select your data Click the [Layout] button -ROW: Type -DATA: Count of Type Select a destination for the Pivot Table Click [OK] Next, on the Pivot Table, double-click on the Type heading to view the settings dialog. Click the [dvance]button Set Autosort: Descending Using field: Count of Type Click [OK] That will list each unique item in the list in descending order of its count. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Ralph" wrote: Hi, Is there a way to select a table and have excel produce a list ranking the # of times each item shows up in the list, i.e. dog 5 cat 2 bird 1 ??? Thanks |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting # of Times an Item Shows Up in a Table
also, if I can email the file let me know, I think it'll make things a lot
easier, THANKS!!!!! "Ron Coderre" wrote: field b2, the entry is "cat, dog, bird", in field c3 the entry is "cat, bird"<< Hmmm....that would have been a really nice piece of information to know right up front.. :\ The complexity of your solution (if there is one) just increased significantly. We'll see what we can do for you. *********** Regards, Ron XL2002, WinXP-Pro "Ralph" wrote: ahhhhhh!!! Thanks Ron & Bpeltzer but you are over my head with this, maybe I'm too excel challenged but I'm finding your instructions a little too complex for me to pull this off. An additional complexity is that in the fields of the table I have items separated by commas, such as in field b2, the entry is "cat, dog, bird", in field c3 the entry is "cat, bird", is this going to screw up your ideas in terms of counting everything?? I wish I could post the file somehow, it would make all this MUCH easier,........ "Ron Coderre" wrote: I know you posted this in the Worksheet Functions forum, but I think a Pivot Table might give you what you're looking for more easily: Select your column of data (Make sure it has a col heading, I'll assume "Type") DataPivot Table Use: Excel List Range: If not already selected, select your data Click the [Layout] button -ROW: Type -DATA: Count of Type Select a destination for the Pivot Table Click [OK] Next, on the Pivot Table, double-click on the Type heading to view the settings dialog. Click the [dvance]button Set Autosort: Descending Using field: Count of Type Click [OK] That will list each unique item in the list in descending order of its count. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Ralph" wrote: Hi, Is there a way to select a table and have excel produce a list ranking the # of times each item shows up in the list, i.e. dog 5 cat 2 bird 1 ??? Thanks |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting # of Times an Item Shows Up in a Table
Here's a possibility....if you have a list of the possible values that could
be in the list. Assuming the list is in Cells A1:A30 B1: dog B2: cat etc C1: =SUMPRODUCT(LEN($A$1:$A$30)-LEN(SUBSTITUTE(UPPER($A$1:$A$30),UPPER(B1),"")))/LEN(B1) Copy that formula down as far as you need That formula returns the number of times the text in col_B appears in Col_A. Is that something you can work with? *********** Regards, Ron XL2002, WinXP-Pro "Ralph" wrote: yikes, Sorry Ron but I really really appreciate your help :) "Ron Coderre" wrote: field b2, the entry is "cat, dog, bird", in field c3 the entry is "cat, bird"<< Hmmm....that would have been a really nice piece of information to know right up front.. :\ The complexity of your solution (if there is one) just increased significantly. We'll see what we can do for you. *********** Regards, Ron XL2002, WinXP-Pro "Ralph" wrote: ahhhhhh!!! Thanks Ron & Bpeltzer but you are over my head with this, maybe I'm too excel challenged but I'm finding your instructions a little too complex for me to pull this off. An additional complexity is that in the fields of the table I have items separated by commas, such as in field b2, the entry is "cat, dog, bird", in field c3 the entry is "cat, bird", is this going to screw up your ideas in terms of counting everything?? I wish I could post the file somehow, it would make all this MUCH easier,........ "Ron Coderre" wrote: I know you posted this in the Worksheet Functions forum, but I think a Pivot Table might give you what you're looking for more easily: Select your column of data (Make sure it has a col heading, I'll assume "Type") DataPivot Table Use: Excel List Range: If not already selected, select your data Click the [Layout] button -ROW: Type -DATA: Count of Type Select a destination for the Pivot Table Click [OK] Next, on the Pivot Table, double-click on the Type heading to view the settings dialog. Click the [dvance]button Set Autosort: Descending Using field: Count of Type Click [OK] That will list each unique item in the list in descending order of its count. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Ralph" wrote: Hi, Is there a way to select a table and have excel produce a list ranking the # of times each item shows up in the list, i.e. dog 5 cat 2 bird 1 ??? Thanks |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting # of Times an Item Shows Up in a Table
thanks Ron, but not really, no, because I don't have a "a list of the
possible values that could be in the list" in any organized way, the information is scattered throughout the table, any other ideas? "Ron Coderre" wrote: Here's a possibility....if you have a list of the possible values that could be in the list. Assuming the list is in Cells A1:A30 B1: dog B2: cat etc C1: =SUMPRODUCT(LEN($A$1:$A$30)-LEN(SUBSTITUTE(UPPER($A$1:$A$30),UPPER(B1),"")))/LEN(B1) Copy that formula down as far as you need That formula returns the number of times the text in col_B appears in Col_A. Is that something you can work with? *********** Regards, Ron XL2002, WinXP-Pro "Ralph" wrote: yikes, Sorry Ron but I really really appreciate your help :) "Ron Coderre" wrote: field b2, the entry is "cat, dog, bird", in field c3 the entry is "cat, bird"<< Hmmm....that would have been a really nice piece of information to know right up front.. :\ The complexity of your solution (if there is one) just increased significantly. We'll see what we can do for you. *********** Regards, Ron XL2002, WinXP-Pro "Ralph" wrote: ahhhhhh!!! Thanks Ron & Bpeltzer but you are over my head with this, maybe I'm too excel challenged but I'm finding your instructions a little too complex for me to pull this off. An additional complexity is that in the fields of the table I have items separated by commas, such as in field b2, the entry is "cat, dog, bird", in field c3 the entry is "cat, bird", is this going to screw up your ideas in terms of counting everything?? I wish I could post the file somehow, it would make all this MUCH easier,........ "Ron Coderre" wrote: I know you posted this in the Worksheet Functions forum, but I think a Pivot Table might give you what you're looking for more easily: Select your column of data (Make sure it has a col heading, I'll assume "Type") DataPivot Table Use: Excel List Range: If not already selected, select your data Click the [Layout] button -ROW: Type -DATA: Count of Type Select a destination for the Pivot Table Click [OK] Next, on the Pivot Table, double-click on the Type heading to view the settings dialog. Click the [dvance]button Set Autosort: Descending Using field: Count of Type Click [OK] That will list each unique item in the list in descending order of its count. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Ralph" wrote: Hi, Is there a way to select a table and have excel produce a list ranking the # of times each item shows up in the list, i.e. dog 5 cat 2 bird 1 ??? Thanks |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting # of Times an Item Shows Up in a Table
Can you create a list of unique items, and enter them in a column?
-- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "Ralph" wrote in message ... i cant seem to get these formulas to work, but i'm very excited that i think there may be a solution here, is it possible that i could email the file to you and you could show me how to arrange those formulas??? or can I post the file somehow to this discussion group?? "RagDyeR" wrote: Maybe not exactly what you're looking for, but ... You could enter this formula in the column adjacent to the datalist, which will simply display the count of every item in the table: =COUNTIF($A$1:$A$100,A1) And copy down. Then, this formula will give you a count of the unique items in the table: =SUMPRODUCT((A1:A100<"")/COUNTIF(A1:A100,A1:A100&"")) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Ralph" wrote in message ... Hi, Is there a way to select a table and have excel produce a list ranking the # of times each item shows up in the list, i.e. dog 5 cat 2 bird 1 ??? Thanks |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting # of Times an Item Shows Up in a Table
I tell you exactly where to put the formulae, B1, B2 and copy down, C1 and
copy down -- HTH RP (remove nothere from the email address if mailing direct) "Ralph" wrote in message ... I am having the same difficulty as with RagDyeR's post which is exactly where to put these formulas. My table is from A1 to G11 so where do I put those formulas Bob? Sorry, I'm a little Excel challenged. The easiest way to communicate all this is if I could somehow post the file to you. Is that possible? Thanks "Bob Phillips" wrote: B1: = A1 B2: =IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""), 0)),"", INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH( 0,COUNTIF(B$1:B1,$A$1:$A$2 0&""),0))) as an array formula, commit with Ctrl-Shift-Enter, and copy down C1: = COUNTIF(A:A,B1) and copy down -- HTH RP (remove nothere from the email address if mailing direct) "Ralph" wrote in message ... Hi, Is there a way to select a table and have excel produce a list ranking the # of times each item shows up in the list, i.e. dog 5 cat 2 bird 1 ??? Thanks |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting # of Times an Item Shows Up in a Table
well .. think you could upload a small sample copy of your file via a free
filehost, and then include a *link* to it in your response here for those interested to take a look at your file. This would also help to keep discussions open to all within the newsgroup, for the benefit of all. Some free filehosts that could be used: http://www.flypicture.com/ http://cjoint.com/index.php http://www.savefile.com/index.php For cjoint.com (it's in French), just click "Browse" button, navigate to folder select the file Open, then click the button centred in the page below (labelled "Creer le lien Cjoint") and it'll generate the link. Then copy & paste the generated link as part and parcel of your query. Note that you should *not* post any attachments direct to the newsgroup -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting # of Times an Item Shows Up in a Table
Ralph wrote:
Hi, Is there a way to select a table and have excel produce a list ranking the # of times each item shows up in the list, i.e. dog 5 cat 2 bird 1 ??? Thanks You might be looking for something like the one my post describes in: http://www.mrexcel.com/board2/viewtopic.php?t=115570 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Calculated field item | Excel Discussion (Misc queries) | |||
Pivot table column duplicates | Excel Discussion (Misc queries) | |||
Counting the number of times a word appears in a worksheet | Excel Worksheet Functions | |||
Pivot table and counting | Excel Worksheet Functions | |||
showing the # of times an item is entered in a column | Excel Worksheet Functions |