Excludging Blank Cells

[Wesley]

I have one column with a formula in multiple rows within the same
column. The formula pulls data from other parts of the spreadsheet or
leaves the cells blank. An example of the formula is =if(A1=0,””,A1).

The outcome is to have a column with data in multiple rows mixed with
blank cells like so.

Bob
Mary
“Blank”
Joe
“Blank”
Jill

What I would like to do is pull the data automatically from this column
putting the data in another column excluding the “Blank” cells like so:

Bob
Mary
Joe
Jill

Any help would be most appreciated.

Thank you,

Wesley


** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software! Free Support at
http://www.ozgrid.com/forum/ **

 

[Don Guillett]

try using data>filte>autofilter>non blanks>

 

[Max]

One way ..

Assuming the col below is in B1:B100
(which are returns by formula)

Bob
Mary
"Blank"
Joe
"Blank"
Jill

etc

Select C1:C100

Put in the formula bar:

=IF(ISERROR(SMALL(IF(B1:B100<>"",ROW(B1:B100)),ROW())),"",INDEX(B:B,MATCH(SM
ALL(IF(B1:B100<>"",ROW(B1:B100)),ROW()),IF(B1:B100<>"",ROW(B1:B100)),0)))

Array-enter with CTRL+SHIFT+ENTER
instead of just pressing ENTER

For the sample above,
you'll get the desired results in C1:C100:

Bob
Mary
Joe
Jill
<Rest of the range are "blanks">

Adapt to suit