Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    group & count function (Excel 97)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: group & count function (Excel 97)

    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.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: group & count function (Excel 97)

    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

  4. #4
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, USA
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: group & count function (Excel 97)

    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.

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: group & count function (Excel 97)

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: group & count function (Excel 97)

    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. <LI>Re-apply the advanced filter
      <LI>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)
    Attached Images Attached Images

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •