Top 5 largest results from list

**LiLi1** · 08-04-2010, 04:30 PM

I have a list of monthly error types an employee can be written up for. There are several error types (there are about 40 types). These do not change the number/type is static.See below what I am calling Chart one...

The amount of errors per month do change. I need to be able to add a section to a report displaying what the top 5 error types were number wise for each month.

I tried using a "large function" which works great to tell me the amount of worst error types for month (for example it looks through the error list and returns these numbers): 33,28,18,14,14. This data is displayed in what I am calling Chart 2 below. So for this example month one error type was reported 33 times, the next worst was 28 reports for another error type etc.

I then tried to use a VLookup on the results of the Large function (i.e. find in list of ~40 errors the number 30 and return the name of error type listed next to it) but there is a problem ..like the above month there are often duplicate numbers (there were 2 error types that both received 14 reports) for differing error types.Any ideas? Below is example of what I have on report page.

Chart 1
Error Type column,Amount column,%Rank column
ErrorA,x,x
ErrorB,x,x,

etc.

Chart 2
Amount column, Error Type
33, Error A
28, Error G
18, Error D
14, N/A
14,N/A

**JBeaucaire** · 08-04-2010, 04:35 PM

Can you please post an actual workbook so we can structure an aid for you directly?

Click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook.

**LiLi1** · 08-04-2010, 05:05 PM

We have a pretty strict internet policy and I try to avoid posting files unless really needed. I added more data to the opening post to try and clarify ... it's a pretty simple worksheet overall.

Let me know if that helps...

**grizzly6969** · 08-04-2010, 10:39 PM

if A1:A40 is amount and B1:B40 is type and C1:C5 is your top 5 then D1:D5
=VLOOKUP(LARGE($A$1:$A$40,1),$A$19:$B$40,2,FALSE)in D1
=VLOOKUP(LARGE($A$1:$A$40,2),$A$19:$B$40,2,FALSE)in D2
=VLOOKUP(LARGE($A$1:$A$40,3),$A$19:$B$40,2,FALSE)in D3
etc. etc.

**grizzly6969** · 08-04-2010, 11:24 PM

sorry will not distinguish duplicate numbers

**MarvinP** · 08-05-2010, 12:02 AM

I'd try to do a Count of Error types for each month and then rank them by the count.
Conditional Formatting also has a top/bottom number and can higlight the cells that fit.

**MarvinP** · 08-05-2010, 12:14 AM

I have an old golf scores sheet. See if ranking the counts fits your problem.

See attached for some example data.

**grizzly6969** · 08-05-2010, 02:03 AM

see if this may help

**DonkeyOte** · 08-05-2010, 04:07 AM

Simplest of all solutions in these scenarios is to use a Pivot Table.

Using Chart 1 as source you would set Error Type as Row Label and Amount Column as Values Field (set to Sum).

You would subsequently apply a Top 5 filter to Type based on Amount and sort by Amount Desc.

If needed use a Dynamic Range as source for the Pivot Table such that the source range expands & contracts with the data.

Attached is a proof of concept - note Pivot created in XL2007.

If you add/remove records to/from Cols A:B and refresh the Pivot you will find the PT reflects the latest dataset.