Sorting in Excel
MikeC
I have one application which generates some outputs in Excel format.
I noticed that one of the columns which shows numbers/integers does
not sort full rows, only the numbers within this column, i.e. when
I click the sorting buttons, the order inside the column changes
between ascending and descending but the rest of the columns
do not change. There are many other columns with numbers and
text and all resort complete rows. There is nothing specific
to this column which would prevent it from sorting like all the
other columns.
The question is: is the application's responsibility to add some
script to excel file in order to sort the columns or the excel
detects the types are sorts accordingly. I am not sure whether
to complain to the company which writes the application or
to Microsoft.
David McRitchie
I guess if you could have described your problem better you
might have answered your own question, kind of hard when
you don't know what the problem is. I'll try to pick out some
possibilities based on words rather than complete descriptions.
IT IS BEST TO SELECT YOUR REGION TO BE SORTED.
For instance select A2:H200
Note when you sort there is a box in lower left corner to indicate
whether your selection includes headers (column/rows).
Sorting on a column does not automatically include keeping
the row data with the column data. If you select Column A and
then sort Col A that is all you sort.
Excel has a little trick that if you select a single cell, and only a
single cell, Excel will extend the range to the current region.
Select the current region CTRL+SHIFT+*
The current region has boundaries at edges of worksheet or up
to a blank column or row boundary (MS definition of ISBLANK).
To include all of the data that is next to another cell with data next
to a selection.
It is best for you to select the region yourself. You may select
the entire spread sheet, or entire columns, only data in the
used range will be sorted. Note if you were writing a macro or use
someone else's macro you want to make sure that processing
is limited to the used area, as opposed to every cell in the
spreadsheet, which may mean that you have to be careful with
what you select, but the sort will always restrict itself
to the used area.
Normally sorting is by column, but you can sort by rows with
the option button, be careful to put it back if you experiment.
NUMBERS ARE SORTED BEFORE TEXT
In ASCII numbers appear in collating sequence (sort order) before
text. Excel extends this a bit further in saying that fields that cells
that are numeric will be sorted before cells that are not numeric.
What you think is numeric is not necessarily what Excel thinks is
numeric. For instance any *number* beginning with a zero is
text.
Sorting on column C yields 9110 9150 0005 1243 1243
when the expected results were 0005 1243 1243 9110 9150
The "0005" appears to be text because for a number to appear
that way you would have to format it. The others are not so
obvious but they too are text rather than numbers because they
are formatted as text.
More of this in
Sorting, Some notes on sorting in Excel
http://members.aol.com/rexx03/excel/sorting.htm
If I missed anything you can probably also find all of the information
in HELP, but this should get you started.
Answers to two questions raised in your last paragraph.
YES, either that or is dependent on your selection range.
There are probably few complaints about SORT not working,
it is pretty well documented by Microsoft. You might check
documentation from the other company before contracting
for changes as it sounds like it was a completed application
or project, that is if what I already wrote did not help.
HTH, Better descriptions of problems result in quicker
responses, essentially writing what almost amounts to a web
page for a reply takes awhile.
(posted only no email copy to messed up email addresses
occasionally a reply posting is delayed or disappears)
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://members.aol.com/dmcritchie/excel/excel.htm
MikeC <user...@remove.hotmail.com> wrote in message
news:38b53c6e....@news.cig.mot.com...
MikeC
Thanks for the response but I did not ask about the sorting details.
I asked about the effect on other values in the same row
My Excel listing has 10 columns and the sorting for just one
column does not behave as for others. There is absolutely
no reason for this column to be unique. It includes numbers
like many of the other columns. Your response is mostly
about sorting - I already specified that this column is
sorted fine. The remaining parts of the rows (in the remaining
columns) stay unchanged. It seems to me it is a bug because
I cannot imagine who would want to sort a column and keep all
the other columns unaffected. The result is totally useless because
you would end up with one column which has no relation to the others.
This is the question.
Patrick Molloy
Your comment is that David's answer is about sorting, but that your question
is not? The subject is then a little misleading isn't it?
My guess is that you find that although a single column is sorted fine,
associated row data in oether columns isn't moved accordingly.
David answered this point also.
To recap. If, within a table, a single column is selected, then a sort will
only apply to that selected column, not to the table. If a single cell
within a table is selected, then excel will select the current region,
generally the table, so a sort will be based on one column, but the entire
table will be affected. Finally, select the entire region for the sort to
work upon, and everything will be sorted. This is useful where there may be
empty row/columns within the table.
AFAIK there are no 'bugs' or 'issues' in this method.
--
Patrick Molloy
Microsoft Excel MVP
www.xl-expert.com pat...@xl-expert.com
_________________________________
MikeC <user...@remove.hotmail.com> wrote in message
news:38b67e8f...@news.cig.mot.com...
David McRitchie
Thanks Patrick & Chrissy for further comments on sorting.
One item left, I can think of many times when you would not
want to include all the columns when sorting one column.
Team1 Team2 Team2
John Alex Brandon
Lars Charlene Courtney
Tiffany Ruth Jan
Also you might have a Phone List (names and numbers) and
have 3 areas. 1) Sorted by first name, 2 sorted by lastname,
3) sorted by phone number.
HTH,
Chrissy R
I'm not sure if your data consists of formulae of if it is
all constants. If you select more than one cell within the
data then sort using the toolbar icon or the sort menu, you
will sort the selected region only. If you do not select
more than one cell you will sort the current region. To see
the current region press Ctrl+Shift+*.
If you sort only one column and the other columns are
formula then they may or may not sort correctly. If you
sort column A and column B contain the formula
= A:A
Or cell B1 contain the formula
= A1
and cell B1 contain the formula
= A2 etc
then column B will sort correctly.
There are some formulae that will cause column B not to sort
correctly (named ranges within the data may cause problems).
If you have only constant values in the other columns then
the only data that will sort is the selected data and there
will be no relationship between this column and the other
columns for each row.
If you select only one cell, Excel knows that you cannot
sort one value so it tries to help by selecting something
reasonable to sort (the current region). If you select a
region (two or more cells, then Excel thinks that you know
what you want sorted so it sorts what you selected.
I can think of no more reasons why Excel would sort
incorrectly. Sometimes Excel dose not know if you have
column headings or not and can do the wrong thing with a
sort. If this happens then select the region to be sorted
and, using menus, select Data > Sort then make the
appropriate choices.
I hope this has helped. If it hasn't you may like to tell
us what is in a line of data - include formulae and constant
values.
--
Cheers - Chrissy.
--
"MikeC" wrote
Denny Campbell
Is it possible that you have empty hidden columns beside the column in
question?
Denny Campbell
Grand Rapids, Michigan
"MikeC" <user...@remove.hotmail.com> wrote in message
news:38b67e8f...@news.cig.mot.com...
David McRitchie
any feedback from this the thread that began with
http://www.deja.com/=dnc/getdoc.xp?AN=589324123
since first reporting that his question was not answered and
the posting may be similar to another posting by someone else
http://www.deja.com/=dnc/getdoc.xp?AN=589298513
the only feedback so far appears was a single reply that
indicated the replies were either not read properly,
missed the point, or not were
not understood (point of view). New replies appear to just
reiterate what was already posted in the hope of
clarifying some replies. Since the poster wishes to remain
anonymous and is not posting back, I hope that means that
the question was answered. Had he not posted that he
didn't like the answer we might assume the question was
answered, but by posting back, getting more answers and
not indicating whether we have yet answered his question
to his satisfaction or not, I think we should just assume
he has his answer.
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://members.aol.com/dmcritchie/excel/excel.htm
>
> "MikeC" <user...@remove.hotmail.com> wrote in message
> news:38b67e8f...@news.cig.mot.com...
> >
> > Thanks for the response but I did not ask about the sorting details.
[clipped]