Eliminating Blank Values

  • Thread starter Thread starter Ken
  • Start date Start date
K

Ken

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?
 
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.
 
Hi,

I think this does what you want:

=INDEX(A$1:A$100,SMALL(IF(ROW(A$1:A$100)*(A$1:A$100<>"")>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,SMALL(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,SMALL(IF(ROW(A$1:A$100)*(A$1:A$100<>"")>0,ROW(A$1:A$100),""),ROW())))

If this helps, please click the Yes button
 
Back
Top