I have an excel file with 1 column, all of text, about 1200 items
long.  This text is types of customers that we have.  The list is
sorted so that all the same types are together.  So, for example, the
first 200 items in the column are all the exact same word.  The next
100 are a different word, but all the same, etc...  I would like to
count each of the unique types to find out how many of each type we
have(there are about 50 different types).  In the past, I have simply
selected all the of the items in a particular group and done the math
in my head, but this is time consuming, and I have to believe that
Excel has a way to do this that I don't know.  So, what I'm looking
for is a formula or filter or whatever that when I apply it to my list
will return results as such:
Type 1    200
Type 2    100
Type 3    75  etc...

Thanks!
dan

---

Request for Question Clarification by pafalafa-ga on 10 Oct 2006 07:21 PDT

tnsdan-ga,

I'm not sure how automatic a process you're looking for, but a simple
way to do this is to use the "Find" feature under the Edit menu.

Just search for the word in question, and select "Find All"  At the
bottom of the results box (in the gray area) will be a message along
the lines of [ 125 cell(s) found ].

And there's your count!


Does that work for you?


pafalafa-ga

---

Clarification of Question by tnsdan-ga on 10 Oct 2006 08:02 PDT

pafalafa-

Thank you for taking a stab at my question.  I think that I am looking
for something more automatic than that (if it exists).  There are
about 50 different types and they are rather long (ex. "200452- Single
Member") so typing each of those 50 times is not really any shorter
than what I'm doing now.

Any other thoughts would be appreciated!!

Thank you!
dan

---

Request for Question Clarification by hummer-ga on 10 Oct 2006 08:18 PDT

Hi tnsdan,

I think this freeware may work nicely for you  -

The Duplicate Master V1.3 
http://members.iinet.net.au/~brettdj/

What do you think?
hummer

Hello Tnsdan,

A comprehensive solution will require at least two steps:
 1 extracting the "unique" types to compare and count
 2 making the counts
The second comment by Asmith1234 attempts to addresss the second step
(incorrectly I believe).

I am going to assume your data is in Column "A" with a title in A1 and
that it is OK to add some formulas to the right (e.g., starting at
column E). If you need this extract on a separate worksheet - please
indicate in a clarification request and I can adapt the instructions
to fit that situation.

[1] Extract the "unique" types of customers.
From what you've described in the clarification, the "type" starts at
the beginning of each entry and ends with a "-". I suggest you enter
the following values / formulas to extract those types:
  E1 - Type
  E2 - =MID(A2,1,FIND("-",A2))
The above formula searches for the "-" using the FIND formula, and
uses that index to extract the characters from A2 up to and including
the "-:.

Select E2 & drag a region to match the last row in column A. Use the menu
  Edit -> Fill -> Down
to repeat that formula on each row. The type of customer (with
trailing -) is now listed in column E. This list should match the
values in column A on a one for one basis (uniques not yet removed).

To get the "unique" customers, enter
 G1 - Type<>" "
and then use the menu
  Data -> Filter -> Advanced Filter...
to bring up a dialog box with a pair of radio buttons, three fields,
and a checkbox. Fill out the form as follows:
  Action - make sure "Copy to another location" is selected
  List Range - $E$1:$E$1200  (or wherever the list of types ends - you
may be able to drag to select this)
  Criteria Range - $G$1
  make sure Unique records only is checked
  Copy To - $F$1
and then OK.

This will copy all the "unique" values of customer types into column F
(including the title "Type").

You need to repeat these steps whenever you create a new customer type
(not when you add or update entries in column A). Also note - these
steps can be recorded into a macro if you update the customer types on
a frequent basis.

[2] Making the counts

I suggest using an "array formula" to generate the counts. You may
want to review the Excel help file / search with the phrase array
formula for more information. The example below uses the "calculate a
single result" alternative if you're looking in the help file.

In cell G2, enter the following as an array formula
  =SUM(IF(MID($A$2:$A$2000,1,LEN(F2))=F2,1,0))
If you click on the checkmark (next to the formula bar) or hit enter
without the proper modifier key (varys by OS - Ctrl on a Windows PC),
you will get an error message or incorrect result. The result should
be the number of values in column A that match the customer type in
cell F2.

Note - the limit on the sum shown above is row 2000 - allowing for
growth in the data in column A "just in case". Adjust that limit if
your customer list continues to grow.

You can now select the region G2 through G51 (or where the unique
customer types ends in column F) and use the menu
  Edit -> Fill -> Down
to copy the formulas into the cells in column G.

At this point, you should have accurate counts of customer types in
column G, adjacent to each customer type in column F.

Note that these counts will remain correct when you update values in
column A. If you update the customer types in column F as described in
[1] - be sure the matching cell in column G has a formula to compute
the count (copy / paste or fill down again).

If some part of the answer is unclear, if the assumptions I've made
are incorrect, or if you need further information on some part of the
answer - please make a clarification request. I will be glad to help
as needed.

Good luck with your work.

  --Maniac

THanks to maniac and all the other commentors and researchers who
chipped in.  I was able to get what I needed.

I haven't signed up as an a question answerer, just discovered this
service so i thought i should check before hand if i am breaking a
rule by answering the question.
In my opinion, yes there is a strait forward solution to your
question. but you will have to type in all the different types into a
column once. after that it will auto update with the number of
entries. you dont need to sort your data into order either.

If no users have an objection, i will tell you what it is.

err, ok, i waited about 10 mins, people seem to be updating this quite
quickley so i guess there were no objections.

the function you want is countif(). you can look it up as a worksheet
function in excel help.

the syntax is =countif(##:##,TYPE_TO_LOOKUP)
where the first parameter is the range of data in your customer list
so you do: first element:final element
you can make this list go beyond the actual range of data if you want
to leave room for the list to change size.
in a column you list once all of the different things to search for
and then refer to an element where i wrote TYPE_TO_LOOKUP

say you have your data in column A and now you are creating a summary
of it in colums B and C, this might be what you want.  the numbers
down the right hand side are cell indicees.

    |A     |B        |C
-----------------------------------------------
1   |...   |TYPE 1   |=countif(A$1:A$1200,B1)
2   |...   |TYPE 2   |=countif(A$1:A$1200,B2)
3   |...   |TYPE 3   |=countif(A$1:A$1200,B3)
... |...   |...      |=countif(A$1:A$1200,...)


hope this helps.

Just add a row above your data list and enter a heading e.g. Type then
select that cell and all the others in that column below it then from
the data menu choose subtotals. Reply OK to the warning then in the
next dialog makesure Type (or whatever your heading was shows in the
At each change in: and Count Appears in the Use Fuction Box and that
Type (or whatever your heading is is ticked in the Add Subtotal For
list and hit Ok and there you go

G

tnsdan:

The easiest way to solve this (6 clicks) is to use pivot tables.

1) Highlight the column of data (it must have a title at the top) or
just the data cells and title (either way works)
2) Select Data\"PivotTable and PivotChart Report..."\Finish.  This
will create a new worksheet with the pivot table wizard running.
3) Drag the title from the PivotTable Field List to the area that says
"Drop Row Fields Here" and drop it
4) Drag the title (yes, again) from the PivotTable Field List to the
area that says "Drop Data Items Here" and drop it
5) The upper left should say "Count of [your column title]".  If it
says "Sum of..." or something else, double-click there and select
"Summarize by: Count"
6) You can highlight the results in the pivot table and "Edit\Copy",
then "Paste Special\Values"

The Subtotals approach suggested by grahamsql-ga isn't bad either -
try both and see which you like...