Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Location
    Palo Alto CA
    Posts
    3

    Unanswered: AlphaNumeric Frequency Distribution -Help

    I need help in performing a frequency dustribution
    using a range of Alpha Characters.
    I know it can be done with the Freq built-in function
    BUT that is only good for numeric data.
    What I want...
    given the following data below
    NY
    CA
    MA
    NY
    CA
    NJ

    I would like to extract from the simple data base above
    the following result
    CA 2
    MA 1
    NJ 1
    NY 2

    A freq distribution so I know the "freq" of the Char strings
    in the data.

    Almost like a Data/Filter AutoFilter EXCEPT
    the results are recorded for me, NOT one by one
    like you have to view with the interactive
    DATA/Filter AutoFilter

    I can do this very easily with a data base package
    like Paradox.

    Thanks

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Suppose your list is in A1:A6, then put the unique values in Column B. In C1 put this formula:

    =COUNTIF($A:$A,B1)

    Then copy this formula down as far as the unique values in column B. (The fastest way is to select C1 and get the cursor in the lower right corner when it turns a dark cross-hairs. Then double-click. It will fill to the bottom of the adjacent column.)
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Oct 2003
    Posts
    1,091
    If you want this even more automatic, i.e. adding more items to column A, then you can define a dynamic range.

    Insert > Name > Define. In the dialog box type a name (no spaces), i.e. StateList, then in the Refers To box at the bottom, type in this formula:

    =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

    Click OK.

    Then change the formulas in Column C to (for C1):

    =COUNTIF(StateList,B1)

    Now, whenever you add a state name in column A, your named range will add it, and your counting formula will automatically note the addition. To test it, add one state (of the four samples you gave) to the list in Column A, and the resultant formula in Column C should note that change.
    Last edited by shades; 12-29-03 at 11:18.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  4. #4
    Join Date
    Dec 2003
    Location
    Palo Alto CA
    Posts
    3
    Shades
    Thank you for your reply!!!
    You seem to be the most knowledgeable
    person I have found to date in EXCEL!!

    I guess I did not totally map out my request.

    What you provided me is GREAT if I know what
    unique occurances I am looking for in the first place.

    The tool I need is where I don't know what
    unique alpha character strings are.
    Your solution is the answer to the second half
    of the answer.

    First I need to know what those unique strings are, in
    the range of values.

    I know you know!!!

    OK, I'll grovel for the answer to the first part of this
    two part solution.
    Here we go...
    please, please, pretty please.

    I feel better now.

    Thanks
    oh great one (that's you Shades)

    -Arnie

  5. #5
    Join Date
    Oct 2003
    Posts
    1,091
    Okay, then assume that you have the character string in column A, then in A1 be sure you have a heading/title for that column (in the illustration, "States" would be fine). Then select all of the entries in Column A (Select A1 then hold down CTRL + SHIFT + Down ARROW).

    Then go to Data > Filter > Advanced Filter. In the dialog box, choose "Copy to another location" then click the box at the bottom "Unique records only", then in the "Copy to" box, choose another spot (say $B$1, provided nothing is in that column already). Then click OK. If you want, while the unique items are now listed in Column B, you could also sort them (Data > Sort).

    Now, Column B will give you only the unique items from Column A, and those can then be referenced in the formula. Doing it this way, I would also name the dynamic range for Column B, as "StateUniq" (as described above) for future reference.


    HTH
    ------------

    BTW, no need to grovel. We are all learning the capabilities of Excel. I'm just glad I could help a little in your learning.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

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