Sorting with blank cells

[Guest]

Help. I'm copying the values only from a four column table on one worksheet
to a second worksheet. The table has blank rows in random locations. On the
new worksheet I do a sort and the blank rows show up at the top of the table
not the bottom. I have no clue why!! Help.

 

[Guest]

Help. I'm copying the values only from a four column table on one worksheet
to a second worksheet. The table has blank rows in random locations. On the
new worksheet I do a sort and the blank rows show up at the top of the table
not the bottom. I have no clue why!! Help.

 

[Guest]

1) Add headers. And if Excel doesn't automatically detect..
2) Data, Sort, data has headers

 

[Guest]

Adding headers and sorting with headers made no difference. When I sort, the
blank rows still show up at the top!!

Another clue - the blank rows look blank and there does not appear to be
anything in the cells - no values, no equations. If I highlight those blank
cells and hit delete (enen though there is apparently nothing there to
delete!!), the subsequent sort puts the blank lines at the bottom as it
should.

What is being deleted from the empty cells?

 

[Dave Peterson]

Saved from a previous post:

If you want to see what's left in that cell after you convert ="" to values,
try:
Tools|Options|Transition Tab|Toggle Transition Navigation keys on.

Then select one of those cells and look at the formula bar. You'll see an
apostrophe. (Don't forget to toggle the setting to off.)

When I want to clean up this detritus, I do this:

Select the range (ctrl-a a few times to select all the cells)
Edit|Replace
what: (leave blank)
with: $$$$$
replace all

Immediately followed by:
Edit|Replace
what: $$$$$
with: (leave blank)
replace all

 

[Guest]

Bingo. I found the apostrophes. Thanks Dave.

Is there any way of setting things up so ="" really equals blank? Cleaning
up is a pain.

 

[Dave Peterson]

Nope.

But if you know that you're going to convert those formulas to values, you could
return something else:

=if(a1=5,"ok","")
could become
=if(a1=5,"ok",na())

Then convert to values and finally replace all the #n/a's to (leave blank).