paste value of "" isn't really blank?

[kgkev]

I have a formula,

if(A2="","")

If I copy and paste this as a value the the cell isn't treated as blank cell, If I sort highest to lowest then the "" values come out on top.

How do I get round this?

 

[Trevor G]

Can you show some sample data so this can be explored.

 

[kgkev]

Example data

If you copy and paste as values then sort by column "C" the "" cells have a high value

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">A/C</td><td style=";">Amount</td><td style=";">Total</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">abc</td><td style="text-align: right;;">10</td><td style="text-align: right;;">60</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">abc</td><td style="text-align: right;;">20</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">abc</td><td style="text-align: right;;">30</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">def</td><td style="text-align: right;;">10</td><td style="text-align: right;;">60</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">def</td><td style="text-align: right;;">20</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">def</td><td style="text-align: right;;">30</td><td style=";"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C2</th><td style="text-align:left">=IF(<font color="Blue">A2=A1,"",SUMIF(<font color="Red">A:A,A2,B:B</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C3</th><td style="text-align:left">=IF(<font color="Blue">A3=A2,"",SUMIF(<font color="Red">A:A,A3,B:B</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C4</th><td style="text-align:left">=IF(<font color="Blue">A4=A3,"",SUMIF(<font color="Red">A:A,A4,B:B</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C5</th><td style="text-align:left">=IF(<font color="Blue">A5=A4,"",SUMIF(<font color="Red">A:A,A5,B:B</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C6</th><td style="text-align:left">=IF(<font color="Blue">A6=A5,"",SUMIF(<font color="Red">A:A,A6,B:B</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C7</th><td style="text-align:left">=IF(<font color="Blue">A7=A6,"",SUMIF(<font color="Red">A:A,A7,B:B</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[pgc01]

I have a formula,

if(A2="","")

If I copy and paste this as a value the the cell isn't treated as blank cell, If I sort highest to lowest then the "" values come out on top.

How do I get round this?

Hi

When you copy->paste values some of the cells will have an empty string. When you sort lower to higher those empty string will be on top, as they should.

What you can do, for ex.:

1 - sort, select the cells on top with the empty strings, press delete (to empty the cells) and sort again

2 - after the copy->paste values run some code that deletes the empty strings

3 - use some formula or code to sort the array ignoring empty strings

4 - in your formula use instead of an empty string some string you don't use, like "###" and after copy->paste values use replace to empty the values

...

 

[Jon Peltier]

Perhaps this analysis is better suited to a pivot table. Keep the original data in a table somewhere, and forget about subtotals. Use a pivot table to sum up and sort values for each category.