XL Sort

[Bob Morrison]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman";} </style> <![endif]--><!--[if gte mso 9]><xml> <o:shapedefaults v:ext="edit" spidmax="1026"/> </xml><![endif]--><!--[if gte mso 9]><xml> <o:shapelayout v:ext="edit"> <o:idmap v:ext="edit" data="1"/> </o:shapelayout></xml><![endif]--> This has probably been asked a thousand times so I’ll apologise before I start.

Anyway the question is: How do you get XL to sort blank cells to the bottom, not the top?

Google has not been my friend on this one, although there are a few highly complicated methods mentioned.

XL 2002

[FONT=&quot]Thanks Bob.[/FONT]

 

[MrsGKL]

I'm not sure about the hierarchy.

As usual, I'm sure there are many ways to skin this cat, but here's mine:

if your data was in column B,
in column A, you could put a formula =IF(TRIM(B1)="","",B1)
copy the formula down
that way if your cell was just had spaces, this would remove them, but it wouldn't change the formatting of the other data in column b.
do copy on column A
paste special values in column B
and that should work out for you.....

I pasted the below def of trim from Excel help.

<TABLE class=OTbl cellSpacing=0 cellPadding=0 width="100%"><TBODY><TR><TD class=cdOTATtl width="100%">TRIM</TD><TD style="PADDING-RIGHT: 0px; PADDING-LEFT: 12px; PADDING-BOTTOM: 0px; PADDING-TOP: 0px"></TD><TR><TD class=ACB style="PADDING-RIGHT: 12px; PADDING-LEFT: 12px; PADDING-BOTTOM: 10px; PADDING-TOP: 10px" width="100%" colSpan=3>Show All
Hide All
Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing.
Important The TRIM function was designed to trim the 7-bit ASCII space character (value 32) from text. In the Unicode character set, there is an additional space character called the nonbreaking space character that has a decimal value of 160. This character is commonly used in Web pages as the HTML entity,  . By itself, the TRIM function does not remove this nonbreaking space character. For an example of how to trim both space characters from text, see Remove spaces and non-printing characters from text.
Syntax
TRIM(text)
Text is the text from which you want spaces removed.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

Note Do not select the row or column headers.

Selecting an example from Help

1. Press CTRL+C.
2. In the worksheet, select cell A1, and press CTRL+V.
3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

<TABLE><TBODY><TR><TD><TABLE class=collapse id=NestedTable1 name="NestedTable1"><TBODY><TR class=trbgeven style="HEIGHT: 0px"><TD> </TD></TR><TR class=trbgodd style="HEIGHT: 0px"><TH>1</TH></TR><TR class=trbgeven style="HEIGHT: 0px"><TH>2</TH></TR></TBODY></TABLE>
</TD><TD><TABLE class=collapse id=NestedTable2 name="NestedTable2"><TBODY><TR class=trbgeven style="HEIGHT: 0px"><TH>A</TH><TH>B</TH></TR><TR class=trbgodd style="HEIGHT: 0px"><TD>Formula</TD><TD>Description (Result)</TD></TR><TR class=trbgeven style="HEIGHT: 0px"><TD>=TRIM(" First Quarter Earnings ")</TD><TD>Removes leading and trailing spaces from the text in the formula (First Quarter Earnings)</TD></TR></TBODY></TABLE>
</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[Bob Morrison]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman";} </style> <![endif]--> Hmmm, the plot thickens.

When I select the range on the column I sort on and select “Blank Cells” I get the message “No Cells were found”.

[FONT=&quot]Clearly there is something in the blank cells that is a hangover from “Paste Special - Values”.
[/FONT]

 

[Bob Morrison]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman";} </style> <![endif]--> Thanks Mrs GKL, I’ll try that tomorrow and get back with the results.

 

[Bob Morrison]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman";} </style> <![endif]--> An update:

I tried your “Trim” suggestion Mrs GKL but it came up with a “Circular Reference” so I'll have to try and sort that out.

I know how hard it is to try and sort out spreadsheets without them being in front of you, so is there anywhere on this forum I can post it so you can see first hand what’s happening?

Cheers

[FONT=&quot]Bob[/FONT]

 

[indiantrix]

Replace ALT+0160 with nothing...replace all.
(this is entered by holding down the ALT key while
pressing the 0160 keys on numeric keypad only)
Hope this ends your agony!
Larry.

 

[Bob Morrison]

Replace ALT+0160 with nothing...replace all.
(this is entered by holding down the ALT key while
pressing the 0160 keys on numeric keypad only)
Hope this ends your agony!
Larry.

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman";} </style> <![endif]--> You’ve got me Larry, I’ve no idea what Alt+0160 is all about.

[FONT=&quot]I’m afraid I’m a greenhorn that probably shouldn’t be on this forum. [/FONT]

 

[indiantrix]

Not a problem, let me be more specific. In your version, click the Edit menu, then choose Replace. This opens up a dialog box with two entry fields: "Find What" and "Replace With." Click in the "Find What" field and do the following: While holding down the ALT key on your keyboard, press the following four keys sequentially: 0 1 6 0 THEN when you release the ALT key, a space character will appear in the field. Actually, it only LOOKS like a space character, but it's really a special kind of space character. Anyway, my suspicion is that this character is what's messing up your sort on the "empty" cells.
To complete the process, after entering the strange space character in the "Find What" field, leave the "Replace With" field completely empty, and click on the "Replace All" Button.
This should find any of these characters in your sheet, and remove them all. If my theory is correct, your sort should now NOT be resulting in the unexpected. If I'm wrong, well...hang in there! Someone else may come along with a better idea. That's what this forum is all about.
Larry.

 

[Bob Morrison]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman";} </style> <![endif]--> Update:

I got rid of the circular reference but unfortunately the “Trim Function” failed to resolve the issue.


I then thought maybe there was a problem with my programme, so I transferred the worksheet to another computer running the same version of XL but exactly the same problem.

Now this will rock some of you, but in desperation I tried opening the said Spreadsheet with Open Office and blow me down if it not only opened it, no problem at all, but it even sorted it correctly and saved it back in .xls format.


[FONT=&quot]So I wonder if there is/was a problem with XL 2002 Ver 10.2614.2525?[/FONT]

 

[Bob Morrison]

Thanks Larry but for some reason ALT+0160 doesn't produce any characters??

Yes, I held down the Alt key and even tried the other Alt key whilst entering the 0160 on the numeric part of the keyboard but nothing? I tried hitting replace all anyway but got told there was nothing to replace.

 

[indiantrix]

Hmmm...It should have placed a space character into the "Find What" field, but not UNTIL you released the ALT key following the 4-key entry. If it didn't work, then I guessed incorrectly, unfortunately. Bummer!
Larry.