# Thread: AlphaNumeric Frequency Distribution -Help

1. Registered User
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

Thanks

2. Registered User
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.)

3. Registered User
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.

4. Registered User
Join Date
Dec 2003
Location
Palo Alto CA
Posts
3
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.

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...

I feel better now.

Thanks
oh great one (that's you Shades)

-Arnie

5. Registered User
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.

#### Posting Permissions

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