| Home |
| Search |
| Today's Posts |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Listing data without blank rows
Many thanks for the info...
Regards "vezerid" wrote: Danny, the formula JMB suggested had the following skeleton: =INDEX(data,SMALL(IF(conditions),ROW()) The IF(Conditions) is generating a virtual array, which only only includes non-blank rows. So, SMALL(..., ROW()) was to find first the smallest, then the second smallest etc, of this array. If the virtual array has fewer elements than the current row, ROW() will try to find an element that does not exist. Also, if your data start in row 10, the formula would start to find the 10th element, then 11th etc. So, first modification was to subtract 9 from ROW(). We used ROW($A$9) in order to show exactly which cell is used to start the data. This leads us to the formula: =INDEX(data,SMALL(IF(conditions),ROW()-ROW($A$9)) The second modification, which led to this length, was to see which part could create the error, and this would be SMALL. This leads us to the general philosophy of trapping errors: =IF(ISERROR(formula),"",formula). This can be simplified if instead of using the entire formula in ISERROR, we use only the offending part. So your formula became: =IF(ISERROR(LARGE(...),"",modifed_formula) which is what you used. HTH Kostis |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| listing data without blank rows | Excel Worksheet Functions | |||
| Inserting a new line when external data changes | Excel Discussion (Misc queries) | |||
| Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
| Copy rows of data to another worksheet where ReturnDate is blank | Excel Discussion (Misc queries) | |||
| Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
Threaded Mode
