

Subject:
Excel Question
Category: Computers Asked by: tnsdanga List Price: $7.00 
Posted:
10 Oct 2006 06:58 PDT
Expires: 09 Nov 2006 05:58 PST Question ID: 772248 
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  
 
 


Subject:
Re: Excel Question
Answered By: maniacga on 10 Oct 2006 09:25 PDT Rated: 
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 
tnsdanga
rated this answer:
and gave an additional tip of:
$2.50
THanks to maniac and all the other commentors and researchers who chipped in. I was able to get what I needed. 

Subject:
Re: Excel Question
From: asmith1234ga on 10 Oct 2006 08:25 PDT 
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. 
Subject:
Re: Excel Question
From: asmith1234ga on 10 Oct 2006 08:47 PDT 
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. 
Subject:
Re: Excel Question
From: grahamsqlga on 10 Oct 2006 09:36 PDT 
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 
Subject:
Re: Excel Question
From: dsa_detroitga on 23 Oct 2006 19:02 PDT 
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, doubleclick 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 grahamsqlga isn't bad either  try both and see which you like... 
If you feel that you have found inappropriate content, please let us know by emailing us at answerssupport@google.com with the question ID listed above. Thank you. 
Search Google Answers for 
Google Home  Answers FAQ  Terms of Service  Privacy Policy 