Filter nonblanks from sheet into single column

[Guest]

Hello all-

I have a range of cells, say A1:B10. All of these cells contain formulas
that conditionally display a value in that cell. So, imagine looking at
A1:B10 and just a handful of the cells are displaying a value. i.e. the
formula in each of the cells resembles: IF(<some condition>,<display
conditional value>,"")

I want to filter all the nonblank cells into another location, and all in
one column preferrably. e.g. A3 shows "foo", A5 shows "squim", B5 shows
"flam", all others in A1:B10 contain the empty string "". I need D13 =
"foo", "squim", "flam". NOTE: Each nonblank entry, even if on the same row,
is placed in a cell of it's own in the resultant column, not combined with
other nonblank entries.

- Autofilter on nonblanks is not exactly what I want since I want to combine
all the nonblanks into another location.
- Also, I tried using ISBLANK to filter the cells, but I guess ISBLANK
considers the presence of a formula to be nonblank despite what is being
shown in the cell.

Thanks for looking!
-brandon

 

[Bernie Deitrick]

Brandon,

You will need to visit

http://www.cpearson.com/excel/noblanks.htm

and follow those instructions twice, once for each column, but put one set
of formulas below the other set, so that you form just one column (with
twice the number of rows, obviously).

Then you will need to follow those instructions a third time, on the column
of the two sets of formulas, to create your final list.

HTH,
Bernie
MS Excel MVP