Consolidating a list?
Posted by Chad Peacock on October 21, 2001 3:56 AM
I'm kindof an Excel idiot, so please forgive me. But here's what I want to do:
I have a big (about 3000 long) list of products that I've sold, taking up one long column. It looks something like this:
1 baby doll
2 bike
3 baby doll
4 baby doll
5 bike
6 bike
7 bike
8 rubber banana
What I want to do is consolidate (correct term?) this list, so that I have a new list of each unique product and the number of times it occurs in the original list-- i.e. a list of the # of each product I've sold.
Using the above example, I'd like to turn that list above into something like this:
baby doll 3
bike 4
rubber banana 1
My original list is about 3000 items long, but with only about 500 unique items in it. So I want to consolidate the 3000 into a list of the 500 unique items, and how many occurances of each of these 500 there are.
How do I do this?? Sorry if my question is poorly articulated-- I'm not all that familiar with the terminology for all this stuff!
Thanks in advance for any help you can give! I have a feeling it's a relatively easy thing to do, but I can't seem to figure it out on my own!
thanks!
-Chad
Posted by Aladin Akyurek on October 21, 2001 6:06 AM
Lets assume that A2:A3000 has your data and A1 contains a label/column heading, say, items.
Activate A2.
Activate Data|Filter|Advanced Filter.
"List Range" in the Advanced Filter window will show $A$2:$A$3000.
Choose "Copy to another location" for "Action".
Enter e.g., $C$2 for "Copy to".
Activate "Unique records only".
Click OK.
In D2 enter: =COUNTIF(A:A,C2)
Copy down the formula as far as needed.
============
Posted by Richard S on October 21, 2001 8:32 PM
You could also try a pivot table. Using Aladins data sample, select cell A2, Select Menu Data
|Pivot Table. The wizard will wlak you throught the steps. As the data is text and not numeric, you should end up with a list of all items, with the count beside them
Richard
