Removing "" cells

[buffalo]

Hi all,

Spreadsheet only please --

I have 1 column of data but some columns are blank… actually the formula each of the cells of this column is =if(CONDITION, DO_THIS, "").

I need to create another column where only the non-blank cells are there.

Thanks.

Simple Example --
Column A

1
2
3
562
46
97


Column B
=if(A1>32, do something to B1, "")
=if(A2>32, do something to B2, "")
..
..


Column C
Will have only the non-"" cells from Column B one after the other .. i.e. no empty looking cells here.

 

[VoG]

Is it?

<b>Sheet14</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">1</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">2</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">3</td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">562</td><td >Woo</td><td >Woo</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">46</td><td >Woo</td><td >Woo</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">97</td><td >Woo</td><td >Woo</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >C1</td><td >=IF(B1<>"",B1,"")</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[mikerickson]

Copy column B and PasteSpecial,Values into column C is another option.

If you you looking to have no blank cells in the result, sorting col C should kick the blanks to the bottom.

 

[Richard Schollar]

A formula approach to do the same could be in col C2 and copied below:

=IF(COUNT($B$2:$B$18)>=ROWS($C$1:$C1),INDEX($B$2:$B$18,SMALL(IF($B$2:$B$18<>"",ROW($B$2:$B$18)-ROW($B$2)+1),ROWS($C$1:$C1))),"")

which must be confirmed with Ctrl+Shift+Enter. Amend the ranges to suit. (ie change B2:B18 to your actual range.

 

[buffalo]

Thanks guys --

Richard SCholar, thats awesome.. thanks