Excel - determining frequency of a non-numerical result in a single column of data

I have been looking at the answer to this question and need a bit of further detail.

https://stackoverflow.com/questions/5972947/frequency-of-strings-in-an-excel-column-pretty-sure-this-requires-vbscript/6482519#6482519

I see answer No. 2, by Chris Neilsen, uses a pivot table to determine the count of each result in the name column. Two questions, how is the "count of name" column produced in the pivot table? Secondly, if another name is added after the pivot table is created, (ie, add Sarah to cell A9), will the table automatically upda开发者_如何学编程te?

I have a single column of non-numerical data that will be updated regularly. I am having difficulty creating a summary of the data showing the frequency of each result, with the stipulation that the summary will also update when items are added to the list. It seems a Pivot Table is the best solution, but I am open to other suggestions.

---

1. Understand Pivot Table

Here is a few links to help you understand better what are Pivot Tables and how you can use them :

• in Excel 2003 : http://www.ozgrid.com/Excel/excel-pivot-tables.htm
• Excel 2007 : check here

You will find there how to create the "count of name" column. If not, please ask for some more information.

2. Update Pivot Table

By the way, you can update pivot table data by right-clicking on the Pivot Table and choose "Update" (you can also use the Exclamation Mark as a button). Some more information can be found here : http://ulearnoffice.com/excel/pivot.htm (Update the data Chapter).

3. Answering your question

Pivot Table seem to be a good solution here. You should try and if it doesn't work, please let us know, maybe we'll be able to help you.

---

1. You create the "Count of Name" column by dragging the "Name" field checkbox into the Values window in the Pivot Table Fields window. When I do this in my version of Excel (2007), it defaults to "Count of Name". If yours says something else, left click on the field in the Values window and select "Value Field Settings". From there you can specify what statistic for Names you want Excel to display.

2. If you add to you data, you can update your pivot table manually by changing the data source. Or (better option), you can use a worksheet change trigger event to update the pivot table automatically. Here is some VBA code for this.

   Private Sub Worksheet_Change(ByVal Target As Excel.Range)
   
   Dim names As Range
   
   'Change 1 to number of your data column (A=1, B=2, ...)
   If Target.Column = 1 Then
       'Change "A1" to the first cell (header) of your column of data. Keep quotes.
       Set names = ActiveSheet.Range("A1", ActiveSheet.Range("A1").End(xlDown))
   
       'Change "PivotTable1" to name of your pivot table. Keep quotes.
       ActiveSheet.PivotTables("PivotTable1").ChangePivotCache _
       ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _ 
       SourceData:=names, Version:=xlPivotTableVersion12)
   End If
   End Sub
   

Enter this code by opening VBA in Excel (Press Alt+F11). In the object browser, right click on the sheet (listed under Microsoft Excel Objects) that includes your data. Select "View Code". Paste in the code, and you should be set.