Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? . |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting the number of unique values in a filtered list | Excel Discussion (Misc queries) | |||
counting the last 3 values not equal to x in a list | Excel Worksheet Functions | |||
Counting unique values in a list generated with the OFFSET functio | Excel Worksheet Functions | |||
Counting the number of values in a list between two specified valu | Excel Worksheet Functions | |||
Counting rows, then counting values. | Excel Discussion (Misc queries) |