Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to sort a range which is calculated by array formula | Excel Discussion (Misc queries) | |||
How to read offset cells from dynamic sort array formula? | Excel Discussion (Misc queries) | |||
Auto Extended Formula not working | Excel Discussion (Misc queries) | |||
An Array To Sort Blank Cell | New Users to Excel | |||
Array... Sort of... | Excel Worksheet Functions |