Creating a Top Occurring List

**dpotter2113** · 06-05-2010, 04:44 PM

Hey everyone,

I have a list of movies that I've viewed over the years, along with release year and director. I've scoured the internet, but haven't been able to find a formula to do the following:

I want to be able to generate a list in Excel 2007 of the Top 10 most frequently occurring Directors (C2:C1400) with in the document. I've found a few formulas that generate the first most frequently occurring, but after that I've hit a brick wall. Any thoughts? Thanks.

**opsman** · 06-05-2010, 05:45 PM

your explanation is a little confusing....can you elaborate and perhaps attach a copy of the document?

generate a list in Excel 2007 of the Top 10 most frequently occurring Directors (C2:C1400) with in the document. I've found a few formulas that generate the first most frequently occurring,

**martindwilson** · 06-05-2010, 05:53 PM

=IF(COUNTIF($C$2:C2,C2)=COUNTIF($C$2:$C$1400,C2),COUNTIF($C$2:C2,C2),0)
then filter top ten is one way

**Palmetto** · 06-05-2010, 06:06 PM

Using column-D as a helper, enter this formula in cell D2 and drag it down to the last used row in column-C

=COUNTIF($C$2:C2,C2)+ROW()/100000

Note: the "+row()/100000" is simply used to insure unique values in the counts by very slightly incrementing the count. It prevent matching duplicate counts in the following formula.

In another cell, starting on row -2 (assumes header) use this formula:

=INDEX($C$2:$C$300,MATCH(LARGE($D$2:$D$300,ROW()-1),$D$2:$D$300,0))

**dpotter2113** · 06-05-2010, 07:03 PM

I tried both recommended methods, but neither produced what I was looking for. The first one gave me an output of 1 and the second gave a single name.

To answer the first response:

C2:C1400 contains a list of Directors, while A1:A1400 contain Film Title and B1:B1400 contains Release Year. What I'm looking for is a formula that I can apply to another set of cells that will give me a list of the most frequently occurring names in the Director column.

I beg pardon, because I'm fairly new to excel and this seems to be way above my skill level.

I've attached jpg images of what I have set up and a mock up of what I'm looking for. Thanks.

**martindwilson** · 06-05-2010, 07:26 PM

see attached 2 ways very similar formula approaches palmettos and mine

**dpotter2113** · 06-05-2010, 09:05 PM

Thanks for the help guys! I got it to work, but I was just wondering for sake of wondering what the D column numbers represent. I'm still learning code for excel, so I don't know most of what I just plugged in. Thanks again for the help!