Rogue cells in column sort

This is a continuation of an earlier post, Sorting a sheet by one column, as 
I cannot reply directly due to no MAPI mail. ..

Thanks for the suggestions, from someone setting up a spreadsheet for the 
first time after 20 years. Here are a few inticacies which may be 
appreciated by the newbies to get this to work, together with the Replies 
received.

1. When entering the column designation in the Tools > Sort > Sort by
box,
be sure to place a colon before the column designation, < :C > in this
case.

2. Ensure that all of the cells in the designated Sort Column have the 
identical format. Somehow, several of mine ended up Protected and everything 
worked better when I selected each of the ones I already had data in, 
right-clicked and selected Selection Properties, and set Cell Protection to 
*Unprotect, and, Data Input to *Labels only.

I still had 2 rows out of 90, near the top of the column, which refused to 
sort correctly, even though all had identical formatting and even after my 
clearing them and entering new data? Clearing the troublesome cells did 
result in their being sorted correctly, as a space. When numbers, in General 
format, were added back into the cells, they sorted back to the original, 
incorrect location.

Unbelievably, when I deleted the troublesome rows, added 2 blank rows back 
in, replicated the formulae relevant from other cells in respective columns, 
and re-entered all the original data fresh into these two rows, they still 
incorrectly sorted with numeral "1" in each, ending up after numerals
"2" 
and "3", in an ascending sort, with all other rows in the column
correct. 
The column and row totals are correct, always. If someone has a thought on 
this, great; otherwise, it will just look a little odd, yet be useful.

John:
> they still 
> incorrectly sorted with numeral "1" in each, ending up after
numerals "2" 
> and "3", in an ascending sort, with all other rows in the column
correct.

Is this the column you chose as Key 1?

Alphabetically, letters sort after numbers. And in a spreadsheet strings 
(letters) sort after numeric values.
Presumably some format setting is causing QP to view the "1" as text
and not a 
number. I can reproduce the effect by typing into a Cell
'1
where the apostrophe forces the 1 to be text, not numeric. It shows up without 
its apostrophe, left-justified as text usually is, and it sorts after numeric 
2 and 3.

> 1. When entering the column designation in the Tools > Sort > Sort by
box,
> be sure to place a colon before the column designation, < :C > in
this case.

I have never done that, and I have never had a problem while sorting.
-- 
Good wishes!
Roy Lewis
C_Tech volunteer
(UK)

John R. Sennett wrote:
> 1. When entering the column designation in the Tools > Sort > Sort by
box,
> be sure to place a colon before the column designation, < :C > in
this case.

Apparently you took Tim's hint literally:
   ... "Put the column you want to sort by in
   the box marked '1st' (column C in your case)"

Although you can enter the column designation by hand, it's awkward.  The 
simple, straightforward method is to select the desired column from the 
drop-down list.  The syntax ":C" is actually an advanced cell
reference concept 
synonymous with the cell range "C1..C10000" on the current sheet
(assuming the 
current sheet has 10,000 rows).  The sole purpose of a colon in your usage is to

flag the following letter as a column identifier, which derives from the basic 
syntax "A:C1" = sheet A, column C, row 1).

> 2. Ensure that all of the cells in the designated Sort Column have the 
> identical format. Somehow, several of mine ended up Protected and
everything 
> worked better when I selected each of the ones I already had data in, 
> right-clicked and selected Selection Properties, and set Cell Protection to

> *Unprotect, and, Data Input to *Labels only.

Neither of these two steps are prerequisites, but they may acquire relevance 
under certain associated conditions, i.e., it's the ASSOCIATED conditions which

give these two issues the APPEARANCE of being relevant in sorting data:

"Cell protection" is ONLY of consequence if "sheet
protection" is enabled; 
otherwise, cell protection is an "unfulfilled promise", i.e., it has
NO effect 
on sort performance.  (By default, ALL cells are protected, and ALL sheets are 
unprotected.  A simple rule of thumb:  unless you have a clear reason for 
enabling sheet protection, don't implement it.)

Setting a data input constraint only affects input of NEW data, not EXISTING 
data.  Again, setting this constraint has no effect whatsoever on sorting 
existing data.  (A simple test using @CELL("type",CellReference) will
illustrate 
this principle in a pre-sort/post-sort scenario.)

> I still had 2 rows out of 90, near the top of the column, which refused to

> sort correctly, even though all had identical formatting and even after my

> clearing them and entering new data? Clearing the troublesome cells did 
> result in their being sorted correctly, as a space. When numbers, in
General 
> format, were added back into the cells, they sorted back to the original, 
> incorrect location.

When a column contains mixed data types (values and labels), there's no such 
thing as an inherently "correct" sort.  You must specify if you want
values 
sorted BEFORE labels or AFTER.  That's an option you set in the Sort dialog, as

is the option of how to treat blank cells.  In that sense, "correct"
and 
"incorrect" sorting is ambiguous, unless specified.

> Unbelievably, when I deleted the troublesome rows, added 2 blank rows back

> in, replicated the formulae relevant from other cells in respective
columns, 
> and re-entered all the original data fresh into these two rows, they still

> incorrectly sorted with numeral "1" in each, ending up after
numerals "2" 
> and "3", in an ascending sort, with all other rows in the column
correct. 
> The column and row totals are correct, always. If someone has a thought on

> this, great; otherwise, it will just look a little odd, yet be useful.

I think we're gonna do a spreadsheet version of "Who's on First?"
unless you 
give more specifics:  Does the Sort-by column contain these formulas?  What is 
the formula?  We don't see what you see.

Cheers,
Uli

something to watch out for when sorting; if the cell being sorted 
contains a formula, sometimes the cells referenced in the formula must 
be absolute and not relative.  i have data that gets stored on one page 
as it gets entered.  i paste link references to the recently entered 
data on pages/cells to be sorted.  those pasted cell references must be 
changed from relative (such as B:D5) to absolute ($B:$D$5) to get them 
to sort properly.

hope this is relevant
steve

John R. Sennett wrote:
> 
> Unbelievably, when I deleted the troublesome rows, added 2 blank rows back

> in, replicated the formulae relevant from other cells in respective
columns, 
> and re-entered all the original data fresh into these two rows, they still

> incorrectly sorted with numeral "1" in each, ending up after
numerals "2" 
> and "3", in an ascending sort, with all other rows in the column
correct. 
> The column and row totals are correct, always. If someone has a thought on

> this, great; otherwise, it will just look a little odd, yet be useful.
> 
> 
>