Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting with blank cells
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting with blank cells
"hofberger" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting with blank cells
1) Add headers. And if Excel doesn't automatically detect..
2) Data, Sort, data has headers |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting with blank cells
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? "Tevuna" wrote: 1) Add headers. And if Excel doesn't automatically detect.. 2) Data, Sort, data has headers |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting with blank cells
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 hofberger wrote: 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? "Tevuna" wrote: 1) Add headers. And if Excel doesn't automatically detect.. 2) Data, Sort, data has headers -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting with blank cells
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" wrote: 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 hofberger wrote: 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? "Tevuna" wrote: 1) Add headers. And if Excel doesn't automatically detect.. 2) Data, Sort, data has headers -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting with blank cells
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). hofberger wrote: 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" wrote: 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 hofberger wrote: 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? "Tevuna" wrote: 1) Add headers. And if Excel doesn't automatically detect.. 2) Data, Sort, data has headers -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index/match - make blank cells return a blank value. | Excel Worksheet Functions | |||
Sorting and Blank Cells | Excel Worksheet Functions | |||
Sorting and Eliminating Blank Cells in Formula range | Excel Worksheet Functions | |||
blank rows and sorting problem..plz help me... | Excel Discussion (Misc queries) | |||
Imported Data creates blank cells that aren't really blank | Excel Worksheet Functions |