sort

O

othellomy

How can I sort the columns in my excel file when it exceeds more than
3? Suppose I have a excel file that has 100 columns and several
thousand rows. How can I sort the file based on 10 columns? Thanks.
 
O

othellomy

Thanks. It breaks the row information. No? For example if I sort them
by column A, B and C. I get a excel file. Then if I sort them by D, E
and F then the rows are not correct anymore. Therefore, I get wrong
information in a row.
Since we are at it, is there possible to generate a sequence? For
example I have a excel file and I want to insert a column to the left
and fill that column with a sequence, so that each row is numbered
form 1 though the highest row number? Thanks again.
 
O

othellomy

Thanks.
Since we are at it, is there possible to generate a sequence? For
example I have a excel file and I want to insert a column to the left
and fill that column with a sequence, so that each row is numbered
form 1 though the highest row number? Thanks again.
 
B

Bob C

If I read your second post correctly.

With all sorting you must select all columns then sort on three columns and
as David susgested the first sort is to the right columns D-E-F then sort on
columns A-B-C.

Without selecting all columns sorting on A-B-C will sort only columns A-B-C
leaving the rest as is, hence jumbelling the rows.

HTH
 
B

Bill Ridgeway

You could have a helper column in which all the data you want to sort are
concatenated. (=A1&B1&C1&D1). But be aware that this is text only so that,
for example date that are formatted as dates will sort OK but dates that
formatted as text and are in the form 05/02/2007 will not sort properly.
They need to be changed to 2007/02/05.

Regards.

Bill Ridgeway
Computer Solutions
 
D

David McRitchie

For the second part of your question on generating a sequence.
See my page on using the Fill Handle
http://www.mvps.org/dmcritchie/excel/fillhand.htm

You do not want to fill your sequence down to the bottom of the
worksheet because you would make a tremendous change
in the size of your worksheet (Used Range Ctrl+End), if
that is what you are getting at. If you have a sequence number
in a column you can sort the worksheet and use the sequence
numbers later to restore the original order.

If you want to refer to the current row number you can use
=ROW() or if you want the row of data =ROW()-1 to leave out header.

If you want to refer to the previous row in a formula as in
a bank balance you would use OFFSET see
http://www.mvps.org/dmcritchie/excel/offset.htm
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
which allows you to insert rows and delete rows without
messing up your formulas.

And Bob is correct you normally want to select all cells
on a page before your sort. The only exceptions really
are if you don't want to include all columns or just want
to sort a smaller rectangular range of data. The expansion of
a single cell selection in a sort is never your friend because
sooner or later it will result in loss of your data (jumbled mess).
The worst feature in Excel and made worse by one order
of magnitude when combined with change of Ctrl+A in Excel 2003.
You can use the gray button at the intersection of the
rows and columns, but unlike the historical Ctrl+A it will
change the location of the active cell (and is not a keyboard
shortcut).

Please try to pick a unique subject that properly describes
the question, Outlook Express combined your question
as if it were in the same thread as another question which
was posted in November. Google Groups is even worse
at keeping threads in order when different threads have
the same subject name.
 
Top