excel help please: showing top 5 records

[zimu]

hey guys,

so i have this excel table, lets say 3 columns.

Organization -> total records -> total overdue records

lets say there are 50 elements.

I want to create a sub-table which automatically populates the top 5 records in terms of organizations that have the most overdue records.

preferably without pivottables and all that but if thats the only way then so be it!

i can get the largest 5 numbers in overdue records easily (large 1->5), and tried doing a vlookup on these to get the orgnames but no luck...

any help appreciated!

 

[armstrda]

to do the vlookup, you will need to have the records sorted in column C. but if you have multiple orgs that have the same number of total overdue records, you will run into issues.

 

[mayest]

The easiest way to do this is to use Index() and Match(), instead of VLookup(). That doesn't require any sorting, but you will still have a problem if there are ties (e.g., two organizations with 25 overdue records). The result will be the same organization being listed twice in your top 5 list. If you are willing to use Tables (in Excel 2007, called Lists in Excel 2003 - but exactly the same thing), then you can simply autofilter the data. Or, you can just apply an autofilter without using Tables (though Tables offer lots of other advantages). Either way, you can filter the overdue records column to show only the top 5 (or top any number you like).

That said, here is the Index() and Match() solution: I assume that your data starts in A2 and ends in C34 (labels are in the first row). Also, in G2 you have your first Large() function, with the rest in G3:G6. Now, in H2 place this formula:

=INDEX($A$2:$A$34,MATCH(G2,$C$2:$C$34,0))

That will return the organization name from column A that has the largest number of overdue records. Copy the formula down to H3:H6 and you are done.

Fixing the problem with ties will require some additional logic that you will need to work out depending on your needs.

I hope that helps.

 

[armstrda]

anyway if you want to do this, you can do it with array functions with the offset funtion.

Lets say you have the data like this:

Column A = organization, COlumn B = total records, Column C = total overdue records

so your data would be in an array from A1:C51. Let's say you're putting your data in a minitable at G1:I6 that is formated like this:

G2:G6 say "First, second, third, fourth, fifth"
H1 = "Total overdue records"
H2:H6 = Large(C:C,row()-1) --- this function will find the largest from 1 to 5 based on the rows 2-6 that the function resides in)
now in I you have the organizations... This is where it gets a little tricky. We will use the offset function as an array function.

Here is the equation you want to put in I2

=OFFSET($A$1,MAX((C:C=H2)*(ROW(C:C)))-ROW($A$1),0)

But instead of hitting enter, you hit ctrl-shift-enter which converts the equation to an array function (it will put {} around it)

Now if you select I2:I6, and hit ctrl-shift-enter, it will fill these cells with this function as the array function. This will get you the organization name based on the value returned by the large function.

But again, if you have a tie, you might not see the exact right organization name come up.