Array extended formula - sort
 

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 = amt0, 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

Array extended formula - sort
 

.. 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),C20),E2-ROW()/10^10,"")
Leave G1 empty. This is the criteria col. It'll flag lines where Amt0**
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: C20.

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"yshridhar" wrote:
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 = amt0, 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

Array extended formula - sort
 

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)),R OW(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" wrote:

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 = amt0, 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

Array extended formula - sort
 

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

"JMB" wrote:

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)),R OW(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" wrote:

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 = amt0, 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