Home |
Search |
Today's Posts |
#1
|
|||
|
|||
SORTING question
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. |
#2
|
|||
|
|||
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 "Rebecca" wrote in message ... 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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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 "Rebecca" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding a KeyID column for sorting | New Users to Excel | |||
question on sorting by last name | Excel Discussion (Misc queries) | |||
Question on sorting dates | Excel Discussion (Misc queries) | |||
sorting question | Excel Worksheet Functions | |||
sorting question | Excel Discussion (Misc queries) |