This is an archived version of this page. The current blog is here
Probably there's some neat built in way to do this, but I don't know it if there is. Suppose you have a list, which may contain multiple copies of each item in it. You want to know how many of each there are.
ie, suppose I have a, b, a, c, b, a, d, c, b - how many of each thing do I have?
Okay, put all the items in a column, then sort it, so that all the a's are together, all the b's etc. Call that column A. Now in column B, put a 1 in row 1, then starting at row 2, use this formula: IF(A2=A1, B1+1, 1) - fill down to the end. The successive cells in the column will count up from 1 to however many of each thing you have.
Ah, but how can you extract the highest numbered item of group of identical items easily? Simple: column C - use this formula: IF(B2<=B1, 1, 0) and fill down - now the highest numbers will be marked with a 1 in column C, and all others will have a 0. Auto-filter on column C and you're done.
I'm not really much of an Excel head, but it's very useful for some things, and I've used this trick no end of times. Saves writing a script to do the same job.
Posted by MFreestone at March 29, 2005 08:55 PM