I have a worksheet that has a running pipeline of loans i.e.:

abc $100
def $150
ghi $120
abc $150
def $50
abc $500
....

on another sheet I have a summary which shows the totals for the month,
average loan amount, etc. I would like to add an area in the second sheet
that lists the top ten accounts. I would like to have them running down a
column i.e.:

#1 abc
#2 def
#3 ghi


Is this possible, how could I go about doing this? I really appreciate any
help.

The functions SMALL(range, n) and LARGE(range, n) return the nth smallest or
largest items items from a range. You can then use this value to find to
which customer this amount belongs to with a VLOOKUP. Unfortunately, VLOOKUP
requires that the column being searched is the leftmost one, and the result
can be any column on its right, therefore you need to duplicate (eventually
hide it) the customer.

So in your case, assuming that you have Sheet2 with column A your customer id,
column B the sum of the loan, then write C2: =A2 and copy down that column,
then in Sheet3 write in cell B2: =LARGE(Sheet2!$B$2:$B$999,Row()-1)
and in cell A2: =VLOOKUP(B2,Sheet2!$B$2:$C$999,2,0)
and copy down the range up to row 11 to get the top ten.

Note that if two loan amounts are *exactly* the same, the VLOOKUP will fail
to return the second customer. To avoid this, I suggest that you add to each
loan amount in sheet2 (may be in a new column) a very small amount like ROW()
/10000000 to make sure that you never run with the same amount twice, then
copy the customer to the right of this column and use this "unique" loan
amount in the LARGE and VLOOKUP formulas.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200707/1

I would set up a list of accounts at the bottom of the first file (column b).
I would then add sumif to calculate the totals per account (Column c)
I would then add rate function (column a) (Descending)

In the second worksheet, I would use vlookup to get the data you want




"Mike" wrote:

I have a worksheet that has a running pipeline of loans i.e.:

abc $100
def $150
ghi $120
abc $150
def $50
abc $500
...

on another sheet I have a summary which shows the totals for the month,
average loan amount, etc. I would like to add an area in the second sheet
that lists the top ten accounts. I would like to have them running down a
column i.e.:

#1 abc
#2 def
#3 ghi


Is this possible, how could I go about doing this? I really appreciate any
help.