SORTING question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi. I'm using MS XP (Home) and MS Excel 2003, and I'm just learning how to
use the program, so please bear with me (what follows below is a little
long-winded). I recently imported a very large Access database file into
Excel. This file has five columns, and one of them is a KeyID column
(numbering 1 to 60,000). I will be sorting this Excel Sheet frequently, but
I need to maintain the exact (original) order of the file for reasons I won't
go into here. I will sort the Sheet, make some changes, then sort on the
KeyID column to return to the original order (which starts at 1).

The tops of the columns are, of course, A, B, etc. Then there is a blank
row, and below this are the former Access headings (which appeared when I cut
and pasted into the Excel Sheet), so the file looks something like this:

A, B, C, D, E, etc.
(blank row here)
A, B, C, D, KeyID (which contains numbers, of course).

After the KeyID I will insert into a new column an English translation of
the Korean words in B and D columns (this new column is labeled "TRA"). Now
as I mentioned when I asked a similar question before, I am very worried
about having the original order disrupted because occasionally I will have to
add new rows and then renumber the KeyID column, which one of the MVPs so
kindly helped me do. My problem is this: I have been experimenting with the
sorting just to see if the order is being maintained, but one time the blank
line at the top disappeared and one of the rows down below ended up at the
top and stayed there, thereby disrupting the original order (I guess it
became a heading or something). Do you follow me here? I'm sorry about
being so windy in my explaination, but I can't show you a picture. Could
someone please give me (or direct me to) some advice or detained information
about sorting in Excel so as to maintain the original order at all times
(just as when I used Access). Thanks.
 
Rebecca
I'm just going to pass along some general (Excel) sorting advice.
When you tell Excel to sort, you can do so in several ways. The
problems that you have run into occur when you tell Excel to sort such that
Excel has to make certain assumptions about what you want sorted and how.
Most of the time, these assumptions are good ones. Sometimes they are not.
You ran into the latter.
The way around this is to tell Excel exactly what you want and how you
want it, and reduce the assumptions to zero.
When you tell Excel to sort a range of rows, and the range contains one
or more blank rows (in the key column), all the blank rows will be put at
the bottom of the range. If you have a blank row at the top, like you do,
then you should tell Excel to sort a range that does not include that blank
row.
So what you need to do is select the range you want to sort before you
start sorting. If your range is relatively small, you can simply select the
range by dragging the first cell down and to the right. But you said you
have some 60,000 rows. You don't want to select that range by dragging the
mouse around. Do this instead: Note the cell address of the top left cell
of the range you want to sort, say A3. Then go down and to the right (not
dragging the mouse) and note the cell address of the last cell of the range,
say Z60000. Now find the name box. The name box is the white box that is
immediately above the "A" of Column A when the Column A width is the default
width. Click in this name box and type in "A3:Z60000" without the quotes.
Hit Enter. You just selected the range A3:Z60000. Now click on Data -
Sort. In the box that appears, make sure that "Header row:" is not selected
unless the range you selected includes a header row. Now select the column
that you want to use as the "Sort by:" column. Note that you can select 2
other columns to sort by in turn. That's it. HTH Otto
 
You may want to look into sorting on a dynamically named range that way
you can add to the data table at any point and still have your sorting
work out for you.
 
Rebecca,

To restore the exact original order, you need to create a record column.
Insert a new column A, select that column, type =ROW(), hit Ctrl+Enter, then
press Ctrl-C, right-click on column A, and select Paste Special.... then
Values, and OK. This puts the original row number as a value into column A.
Then, when you need to restore the original order, sort based on column A -
just make sure that all your sorting includes column A to begin with.

HTH,
Bernie
MS Excel MVP
 
Back
Top