complicated array

[boris]

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.

 

[Domenic]

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!

 

[boris]

in theory, something along those lines. Problem is that
this assumes you have unique values to rank, whereas I am
looking for it to sum, within the array itself the amounts
of all the listed accounts BY account, then evaluate THAT
virtual list for the rank. Any more ideas on how to do
that?

 

[Max]

One way, if you don't mind a bit of "distributed processing" <g>,
try this non-array set-up ..

Assume the source data below is in Sheet1,
cols A and B, data from row2 down

Acct Amount
A 10
B 20
C 12
D 45
E 34
F 5
G 45
A 5
C 10
D 8

etc

We'll use 4 empty cols to the right (D to G)

Put:

In D2: =IF(A2="","",IF(COUNTIF($A$2:A2,A2)>1,"",ROW()))

In E2:
=IF(ISERROR(SMALL(D,ROWS($A$1:A1))),"",INDEX(A:A,MATCH(SMALL(D,ROWS($A$1
:A1)),D,0)))

In F2: =IF(E2="","",SUMIF(A:A,E2,B:B))
In G2: =IF(F2="","",F2-ROW()/10^10)

Select D2:G2 and copy down to say G1000
to cover the max expected data range in cols A and B

(Col E will read col D to drive out a list of unique accts, col F sums the
amounts up by the unique accts in col E, and col G will act as an arbitrary
tie-breaker col in case there are tied amounts in col F)

In Sheet2
------------
With the same headers in A1:B1 : Acct Amount

Put in A2:

=IF(ISERROR(LARGE(Sheet1!$G:$G,ROWS($A$1:A1))),"",INDEX(Sheet1!E:E,MATCH(LAR
GE(Sheet1!$G:$G,ROWS($A$1:A1)),Sheet1!$G:$G,0)))

Copy across to B2, fill down to B1000
(cover the same range as in Sheet1)

Cols A and B will return a *full* descending sort of all the accounts by
amount, with accounts having tied amounts (if any) appearing in the same
relative order that they are in Sheet1

For the sample data in Sheet1, you'll get:

Acct Amount
D 53
G 45
E 34
C 22
B 20
A 15
F 5
( rest are blanks: "" )

 

[RagDyer]

You could try this set-up.

First, create a master list of all possible accoumts, and then use that list
to sum the order amounts.

Accounts with orders in Column A
Order amounts in Column B
Data in A2:B50

Master account list in J1:J25
Enter this in K1:
=SUMIF($A$2:$A$50,J1,$B$2:$B$50)
And copy down to K25
This gives you a total by customer.

You now use this datalist (J1:K25) to create your ranking display.

In say G2, enter this:
=LARGE($K$1:$K$25,ROW(A1))
And copy down 5 rows to G6.
This formula may return ties, so the next formula to return the account
names will have to be a little complex, so that duplicate account names are
*not* returned.

Enter this *array* formula in F2 to get the account names:
=INDEX($J$1:$J$14,LARGE(IF($K$1:$K$14=G2,ROW($J$1:$J$14)),COUNTIF(G2:$G$6,G2
)))
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

And copy this down to F6.
Ties will display the account names in the order that they were entered in
the master list.
If you might like the ties displayed in the reverse order that they were
entered into the master list, simply replace the LARGE() function with the
SMALL() function.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


in theory, something along those lines. Problem is that
this assumes you have unique values to rank, whereas I am
looking for it to sum, within the array itself the amounts
of all the listed accounts BY account, then evaluate THAT
virtual list for the rank. Any more ideas on how to do
that?

 

[Aladin Akyurek]

in theory, something along those lines. Problem is that
this assumes you have unique values to rank, whereas I am
looking for it to sum, within the array itself the amounts
of all the listed accounts BY account, then evaluate THAT
virtual list for the rank. Any more ideas on how to do
that?

There is no such assumption. If you want to apply a formula system
(instead of pivot tables), here is how to proceed...

1] Extract a list of distinct accounts and total their amounts.

2] Apply the formula system for extracting the Top 5 list, described in
the link Domenic quoted.

The first part also can be obtained with a formula system. What follows
shows how...

Let A3:B13 house the sample of accounts and corresponding amounts you
provided, including the labels Acct and Amount.

In C2 enter: 0

which is needed.

In C3 enter: Idx

which is just a label.

In C4 enter & copy down:

=IF((A4<>"")*ISNA(MATCH(A4,$A$3:A3,0)),LOOKUP(9.99999999999999E+307,$C$2:C3)+1,"")

In D2 enter:

=LOOKUP(9.99999999999999E+307,C4:C13)

In D3 enter: D-Acct

which is just a label.

In D4 enter & copy down:

=IF(ROW()-ROW(D$4)+1<=$D$2,LOOKUP(ROW()-ROW(D$4)+1,$C$4:$C$13,$A$4:$A$13),"")

In E3 enter: Total

which is just a label.

In E4 enter & copy down:

=SUMIF($A$4:$A$13,D4,$B$4:$B$13)

D3:E10 now houses the data to which you'd apply the formula system for
extracting a Top N list.