Counting Values in a list
 

Hi
I have a list of 7766 text strings in an excel sheet. I would like to
count the frequency of each string and create a new table.

For example, had....

Cat
Dog
Hamster
Dog
Horse
Cat
Hamster
Hamster

....in my list, I'd like to produce a table

Cat 2
Dog 2
Hamster 3
Horse 1

The problem is my list has almost 8000 entries and about 4000
varieties of animal. The strings also aren't words (animals). I have
managed to filter the list to remove duplicates and placed them in a
new column. I then used COUNTIF to count how many time each value in
that column appears in the original column, however, some cells are
giving a #VALUE error and some are fine. I have no idea why.
Could I do this by Pivot table?

Counting Values in a list
 

Yes, a PivotTable would be the way to go. Assuming the different types are in
a column labeled "Names" you could put Names into the Row area and also into
the Data area, and do a count.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Tony Brooks" wrote:

Hi
I have a list of 7766 text strings in an excel sheet. I would like to
count the frequency of each string and create a new table.

For example, had....

Cat
Dog
Hamster
Dog
Horse
Cat
Hamster
Hamster

....in my list, I'd like to produce a table

Cat 2
Dog 2
Hamster 3
Horse 1

The problem is my list has almost 8000 entries and about 4000
varieties of animal. The strings also aren't words (animals). I have
managed to filter the list to remove duplicates and placed them in a
new column. I then used COUNTIF to count how many time each value in
that column appears in the original column, however, some cells are
giving a #VALUE error and some are fine. I have no idea why.
Could I do this by Pivot table?
.

Counting Values in a list
 

Try using a Pivot Table, it will give you exactly what you need.

http://peltiertech.com/Excel/Pivots/pivotstart.htm
--
Gary''s Student - gsnu201001


"Tony Brooks" wrote:

Hi
I have a list of 7766 text strings in an excel sheet. I would like to
count the frequency of each string and create a new table.

For example, had....

Cat
Dog
Hamster
Dog
Horse
Cat
Hamster
Hamster

....in my list, I'd like to produce a table

Cat 2
Dog 2
Hamster 3
Horse 1

The problem is my list has almost 8000 entries and about 4000
varieties of animal. The strings also aren't words (animals). I have
managed to filter the list to remove duplicates and placed them in a
new column. I then used COUNTIF to count how many time each value in
that column appears in the original column, however, some cells are
giving a #VALUE error and some are fine. I have no idea why.
Could I do this by Pivot table?
.