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 thought Excel generally does sort blank cells to the bottom?

 

[Dryver14]

I have had this problem before when the cell is not blank it has copied across "" in the formula

one way to deal with it is to go to special, select all blanks and replace with 0

 

[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]--> Ok, just to go a bit further, the data that has to be sorted gets there by taking a selection complete with formulas, copying, and using Paste Special - Values to get them into the sort area.

There are no formulas in the sort area at all and it sorts as required except the blank cells get sorted to the top even though I’m sorting on descending values.

[FONT=&quot]In other words, sorting numbers from 0:100 with 20 blanks, the sort rates the blanks higher than 100 and the zero is in its proper place at the bottom.

Got me licked.
[/FONT]

 

[MrsGKL]

Do you think maybe there are actually spaces in the cells and you just see them as being blank?

If that's the reason why, maybe you could use the trim formula.

 

[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]--> Ok, I think I’ve narrowed the field a bit.

It now seems that the “Paste Special” command is leaving something behind that’s not visible which is causing the problem.

Because, if I highlight and delete the contents of the supposedly empty cells in the column I’m sorting on, it then sorts correctly.

I tried using “Paste Special Text” instead “Paste Special Values” but that didn’t help.

[FONT=&quot]She’s an odd ball this one.
[/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]--> Just another note.

If I change the sort to Ascending from Descending it sorts correctly.

And yes Dryver14, there is “” in the source column where it’s copied from which seems to tie in with your experiences.

[FONT=&quot]However, I was hoping to find a way other than having empty cells full of zeros.[/FONT]

 

[MrsGKL]

Did you try this??

Do you think maybe there are actually spaces in the cells and you just see them as being blank?

If that's the reason why, maybe you could use the trim formula.

 

[Bob Morrison]

Did you try this??

Do you think maybe there are actually spaces in the cells and you just see them as being blank?

If that's the reason why, maybe you could use the trim formula.

<!--[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]--> Well there could be spaces but I’ve no idea where they would have come from.

Would you be good enough to explain the Trim Formula as I’m not familiar with it at all?

Does a space rank higher than any number in the sort hierarchy?

Thanks

Bob

 

[Dryver14]

Try go to special select blanks and then hit the delete key