Eliminating Blank Values
 

I have a list in Column A of 100 values, from cell A1 to A10. Several of the
cells are empty (=" "). How can I arrange Column B so that it includes in the
same order all the values of Column A, but excluding the empty cells?
--
Ken

Eliminating Blank Values
 

In B1 enter:

=IF(ROWS($1:1)<=COUNTA($A$1:$A$100),INDEX($A$1:$A$ 100,SMALL(IF($A$1:$A$100<"",ROW($A$1:$A$100)-MIN(ROW($A$1:$A$100))+1),ROWS($1:1))),"")

This is an array formula that must be entered with CNTRL-SHFT-ENTER rather
than just the ENTER key.

Then copy B1 and paste down as far you you need.
--
Gary''s Student - gsnu200812


"Ken" wrote:

I have a list in Column A of 100 values, from cell A1 to A10. Several of the
cells are empty (=" "). How can I arrange Column B so that it includes in the
same order all the values of Column A, but excluding the empty cells?
--
Ken

Eliminating Blank Values
 

Hi,

I think this does what you want:

=INDEX(A$1:A$100,SMALL(IF(ROW(A$1:A$100)*(A$1:A$10 0<"")0,ROW(A$1:A$100),""),ROW(A1)))

This formula wil return #NUM errors after it has found all items. You can
enhance the formula to avoid this:

=IF(ROW(A1)COUNT(A$1:A$100),"",INDEX(A$1:A$100,SM ALL(IF(ROW(A$1:A$100)*(A$1:A$100<"")0,ROW(A$1:A$ 100),""),ROW(A1))))

If the formulas are going to start on the same row you can simplify this to:

=IF(ROW()COUNT(A$1:A$100),"",INDEX(A$1:A$100,SMAL L(IF(ROW(A$1:A$100)*(A$1:A$100<"")0,ROW(A$1:A$10 0),""),ROW())))

If this helps, please click the Yes button
--
Thanks,
Shane Devenshire


"Ken" wrote:

I have a list in Column A of 100 values, from cell A1 to A10. Several of the
cells are empty (=" "). How can I arrange Column B so that it includes in the
same order all the values of Column A, but excluding the empty cells?
--
Ken
--
Thanks,
Shane Devenshire


"Ken" wrote:

I have a list in Column A of 100 values, from cell A1 to A10. Several of the
cells are empty (=" "). How can I arrange Column B so that it includes in the
same order all the values of Column A, but excluding the empty cells?
--
Ken