Re: How to not show blanks when condition isn't met
"Eliminating blank cells in a range"
Taken from
http://www.cpearson.com/excel/noblanks.htm
Worksheet Formulas
The image to the left illustrates a range named [FONT="]BlanksRange that contains a combination of values and blank cells. Although the values are in alphabetical order, this is by no means necessary. It is for illustration only. The values will be extracted and will appear in the no-blanks range in the order in which the appear in the original data.[/FONT]
To use the formula, paste it into the first cell of [FONT="]NoBlanksRange and then copy it down to fill that range. The [FONT="]NoBlanksRange[/FONT] should have as many rows as [FONT="]BlanksRange[/FONT]. Any unused cells in [FONT="]BlanksRange[/FONT] will contain empty values. This is an array formula, so you must press [FONT="]CTRL SHIFT ENTER[/FONT] rather than just [FONT="]ENTER[/FONT]when you first enter the formula and whenever you edit it later, but you do not array enter it into the entire range at once. Array enter the formula into the first cell of [FONT="]NoBlanksRange[/FONT] and then fill down to the last cell of [FONT="]NoBlanksRange[/FONT]. The formula is:[/FONT]
=IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-
COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL(
(IF(BlanksRange<>"",ROW(BlanksRange),ROW()+ROWS(BlanksRange))),
ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))
The formula above is split into several lines for readability. In practice, it should be entered as a single line. A simpler method is available in Excel 2007 and later versions, using the [FONT="]IFERROR function.
[FONT="]=IFERROR(INDEX(BlanksRange,SMALL((IF(LEN(BlanksRange),ROW(INDIRECT("1:"&ROWS(BlanksRange))))),ROW(A1)),1),"")[/FONT]
Enter this formula into the first cell of [FONT="]NoBlanksRange[/FONT] and copy it down through the last cell of [FONT="]NoBlanksRange[/FONT]. Like the other formulas, this is an array formula, so enter it with [FONT="]CTRL SHIFT ENTER[/FONT]rather than just [FONT="]ENTER[/FONT]. This formula is for extracting the non-blank elements to a vertical range -- a range in a single column that spans several rows. If you want the results in a single row spanning several columns, use the following array formula, where the result range is named [FONT="]NoBlanksRow[/FONT].[/FONT]
=IF(COLUMN()-COLUMN(NoBlanksRow)+1>ROWS(BlanksRange)-
COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL(
(IF(BlanksRange<>"",ROW(BlanksRange),COLUMN()+ROWS(BlanksRange))),
COLUMN()-COLUMN(NoBlanksRow)+1),COLUMN(BlanksRange),4)))
Array enter this formula into the first cell of [FONT="]NoBlanksRow and fill to the right through the last cell of[FONT="]NoBlanksRow[/FONT].[/FONT]
