Counting Values in a list
in [Functions]
|
Prev: Index on pivot report filter
Next: Extracting the latest info from 1st sheet and placing into a 2nd s
From: Tony Brooks on 22 Feb 2010 07:13 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?
From: Luke M on 22 Feb 2010 08:48 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? > . >
From: Gary''s Student on 22 Feb 2010 08:50
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? > . > |