Remove Blanks from a Column with Formula

[IainRourke]

Hi,

I have a column of data like this:

(COLUMN B)
0
41.44930565
29.71363323
29.71363323



60.26418424
29.71363323
29.71363323



55.97371796
55.97371796

Where the empty cells are actually "" (returned from a previous formula).
What combination of functions should I use if I want to remove the ""s from this list and return the result in a new column? Column C should look like:

(COLUMN C)
0
41.44930565
29.71363323
29.71363323
60.26418424
29.71363323
29.71363323
55.97371796
55.97371796

I think I need to use a combination of INDEX, MATCH and COUNTIF functions, but I don't know the order in which to use them.

Thanks in advance,

Iain

 

[Mike Szczesny]

Can you use something like this? Assume my data range is A1:A7 In another cell place =

=IFERROR(INDEX($A$1:$A$7,SMALL(IF($A$1:$A$7<>"",ROW($A$1:$A$7)-ROW($A$1)+1),ROWS($C$1:C1)))," ") Enter with Cntrl+Shift+Enter

<tbody>
</tbody>

 

[KyleX]

Can you use something like this? Assume my data range is A1:A7 In another cell place =

=IFERROR(INDEX($A$1:$A$7,SMALL(IF($A$1:$A$7<>"",ROW($A$1:$A$7)-ROW($A$1)+1),ROWS($C$1:C1)))," ") Enter with Cntrl+Shift+Enter

<tbody>
</tbody>

I like it. Creative solution.