Counting # of Times an Item Shows Up in a Table

R

Ralph

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
 
R

Ralph

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!
 
R

RagDyeR

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!
=====================================================

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
 
B

Bob Phillips

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)
 
R

Ralph

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

bpeltzer

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.
 
R

Ron Coderre

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

Data>Pivot 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
 
R

Ralph

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
 
R

Ralph

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 said:
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")

Data>Pivot 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 said:
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
Click to expand...
Click to expand...
 
R

Ralph

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 said:
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")

Data>Pivot 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 said:
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
Click to expand...
Click to expand...
 
R

Ron Coderre

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 said:
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 said:
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")

Data>Pivot 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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
R

Ralph

yikes, Sorry Ron but I really really appreciate your help :)

Ron Coderre said:
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 said:
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 said:
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")

Data>Pivot 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


:

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
Click to expand...
Click to expand...
Click to expand...
 
R

Ralph

also, if I can email the file let me know, I think it'll make things a lot
easier, THANKS!!!!!

Ron Coderre said:
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 said:
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 said:
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")

Data>Pivot 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


:

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
Click to expand...
Click to expand...
Click to expand...
 
R

Ron Coderre

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 said:
yikes, Sorry Ron but I really really appreciate your help :)

Ron Coderre said:
field b2, the entry is "cat, dog, bird", in field c3 the entry is "cat,
Click to expand...
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 said:
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,........


:

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

Data>Pivot 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


:

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
Click to expand...
Click to expand...
Click to expand...
 
R

Ralph

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 said:
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 said:
yikes, Sorry Ron but I really really appreciate your help :)

Ron Coderre said:
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


:

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,........


:

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

Data>Pivot 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


:

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
Click to expand...
Click to expand...
Click to expand...
 
R

RagDyeR

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 !
----------------------------------------------------------------------------
-------------------

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

Bob Phillips

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)
 
M

Max

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
 
Top