Array extended formula - sort

[yshridhar]

Hello everybody
The following is the debtors list
Invoice Debtor Amt Date Age
T001 DEF 140 11/09/2007 118
T002 BQE 0 25/12/2007 0
T003 ABC 100 25/12/2007 13
T004 ABC 0 02/05/2007 0
T005 ABC 20 11/09/2007 118
Age = amt>0, today()-date

I want to list out the debtors list where amount > 0, in descending order
on age. The array extended formula returned the following data.
Invoice Debtor Amt Date Age
T001 DEF 140 11/09/2007 118
T003 ABC 100 25/12/2007 13
T005 ABC 20 11/09/2007 118
The formula for invoice column is
=IF(ROWS($1:1)<=COUNTIF($C$2:$C$10,">0"),INDEX(A$2:A$10,SMALL(IF($E$2:$E$10<0,ROW(A$2:A$10)-MIN(ROW(A$2:A$10))+1),ROWS($1:1))),"")
The other data are extracted using vlookup()
However i want the formula to list out the debtors list sorted on age. Any
suggestions.
Thank you all
with regards
Sreedhar

 

[Max]

.. list out the debtors list where amount > 0,

in descending order on age.

Here's one way to achieve it using non-array formulas ..

Illustrated in this sample:
http://www.freefilehosting.net/download/3a519
AutoList Debtors for amt more than zero n sort desc by Age.xls

Source data assumed in cols A to E, data from row2 down,
where col C = Amt, col E = Age

In G2:
=IF(AND(ISNUMBER(C2),C2>0),E2-ROW()/10^10,"")
Leave G1 empty. This is the criteria col. It'll flag lines where Amt>0**
with arb numbers which uses the Age [col E] values to produce "tiebreaker"
values for the required descending sort which will be done in cols I to M
**with an added precautionary check that the value in Amt is a number. Any
text inadvertently entered in Amt col would return a spurious TRUE if we just
use the check: C2>0.

In H2:
=IF(I2="","",ROWS($1:1))
Just an additional row numbering col to come in handy here for the auto-list
pulled out in cols I to M

In I2
=IF(ISERROR(LARGE($G:$G,ROWS($1:1))),"",INDEX(A:A,MATCH(LARGE($G:$G,ROWS($1:1)),$G:$G,0)))
Copy I2 to M2. Then just select G2:M2, fill down to cover the max expected
extent of source data, eg down to M200. Format col L as date, minimize col
G's width (or hide it away). Cols I to M returns the required automated
results, with lines auto-numbered in col H. Lines with identical age, if any,
will be listed in the same relative order that they appear within the source.
Adapt to suit.

---

 

[JMB]

You could also try:

=IF(ROWS($1:1)<=COUNTIF(C$2:C$10,">0"),INDEX(A$2:A$10,SMALL(IF(E$2:E$10=LARGE(E$2:E$10,ROWS($1:1)),ROW(A$2:A$10)-MIN(ROW(A$2:A$10))+1),COUNTIF(E$2:E$10,">="&LARGE(E$2:E$10,ROWS($1:1)))-ROWS($1:1)+1)),"")

 

[yshridhar]

Thanik You Max and JMB. It worked for my data.
With regards
Sreedhar