Re: complicated array

From: Domenic (domenic22_at_sympatico.ca)
Date: 03/01/05 

Date: Mon, 28 Feb 2005 20:50:19 -0500

Have a look at Aladin's contribution here...

http://www.excelforum.com/showthread.php?t=333697

The example returns a Top 3 list. For a Top 5 list, enter 5 in D1
instead of 3.

Hope this helps!

In article <260d01c51dfb$1d47f8e0$a401280a@phx.gbl>,
 "boris" <anonymous@discussions.microsoft.com> wrote:

> I saw something like this in a book once, but don't know
> which book. It was pretty complex when I saw it, but know
> that it, within itself, sorted and did some of the
> functions I need. The problem:
>
> List of multi-column entries for a sales order. Two of
> the columns are account name and amount of order. I need
> an array (multi-cell, or single cell, with reference to a
> neighboring "counter" cell) which will look at the list
> (based on named ranges which are sized the same - for
> sumif purposes - for both the account column and amount
> column) and produce for me the top 5 accounts by total
> amount. In other words, from the following list:
>
> Acct Amount
> A 10
> B 20
> C 12
> D 45
> E 34
> F 5
> G 45
> A 5
> C 10
> D 8
>
> I need the section where this array would be to look as
> follows:
>
> Acct Amount
> D 53
> G 45
> E 34
> C 22
> B 20
>
> And with each new additional line of entry (again, which
> would capture into the names range), that last summary
> table would need to reflect the top 5. The "counter" cell
> I referred to means that to the left or right of this, I
> am able to put a static 1, 2, 3, etc. so that each formula
> could refer to that cell to understand its associated
> position in the "virtual" list I am looking for this
> formula to create.
>
> As I see it, the steps for the array are:
>
> 1) get the sumif totals of all the cells, based on account
> 2) find the Nth max number (1st, 2nd, etc.)
> 3) produce that Nth's acct name and amount in separate
> columns
>
> If someone can get this, you're a star. Like I said, I
> know I've seen something that did a similar sort-in-place
> and show the descending order of results array. But that
> was in a book, and I don't know which book.
>
> Thanks. Boris.