group & count function (Excel 97)

Topic

New Reply

Does there exist a function that will read a list of numbers, pick out all the different/unique values and give a count of how many times that value occurs in the list. (i get a report daily that lists phone numbers (10digits). i use the left function to cull out the area code, but then i need to group by area code and see how many numbers i have from each area code that exists in the list). Any ideas are much appreciated.

Reply | Quote

Replies

A reasonably fast way is to create a pivot table.

(Note: my wording may be slightly off, since I use a foreign-language version of Excel)

Make sure that there is a header above the list of zip codes, say “ZIP”.
Select the header together with the zip codes (if the data are in a column separate from other data, it is sufficient to select the header).
Select Data/Pivot Table.
Click Next 2 times, until you are at Step 3 of the Pivot Table Wizard.
Drag the header (ZIP) to the Row box.
Drag the header to the Data box too. It should show as Sum of ZIP.
Double click Sum of ZIP and change the function to Count instead of Sum.
Click Next. Decide whether you want the pivot table in a new worksheet or not – it’s up to you.
Click Finish

When the data change, you can refresh the pivot table using the refresh button (wth the exclamation mark) in the Pivot Table tool bar.

Reply | Quote

WORKS GREAT! – Is there an easy way to enter a value as criteria (a date)? in most cases i need to get the data since a certain date. or is it easier to recreate the pivot table each time and select the values manually? Jenn.

Reply | Quote

You can use the original data as a Database range.
Create a Criteria range and a Copy To range.
Use Advanced Filter to copy records filtered by the criteria.

Next, define a name for the pivot table range that will expand and shrink automatically.
For example, if the filtered area code column starts in G1, define a name MyArea as

=Offset($G$1,0,0,CountA($G:$G),1)

This means: start at cell G1, move 0 rows down and 0 columns right, use as many rows as are used in column G, and use 1 column. (If you do a search on “Automatically expand” or something similar, you’ll find more info on creating auto-expanding names)

Finally, create a pivot table based on this name MyArea instead of a literal range.

Now, when the data or criteria change, you’ll have to take two steps:

1. Re-apply the advanced filter
2. Refresh the pivot table

This is probably confusing. Hopefully the attached picture helps. (The captions in the pivot table are in Dutch, I’m afraid)

Reply | Quote

If you are familiar with macros, have a look at John Walkenbach’s way of extracting unique items from a range or array: see this website

Reply | Quote

Another way (if I follow your request) would be to use Data, Filter, Advanced, select your source and output ranges, check Copy to Another Location, check Unique Records only, run the filter, then next to each output cell which should contain a unique phone number, use
=COUNTIF(source_range,individual_output_cell)

Not as good as a pivot table in that source range and the =COUNTIF formula range have to extended down as the range of numbers grows.

Reply | Quote