Original Sort/Data Entry Order

[Marie Robinson]

I sorted my nearly 7,000 entries to add a detail in one column and saved the
addition. How do I return to the original data-entry order and keep that as
the default order?

 

[Dave Peterson]

I use a helper column of cells.

Insert a new column A.
Fill each cell in that column with a formula
=row()
convert those formulas to values
Select the range, then edit|copy followed by edit|paste special|values.

Then sort the data as much as you want (include all the columns).
Make your changes.
And resort the data by that helper column.
Then delete the helper column.

 

[Shane Devenshire]

Hi,

Here is a quick way to add a default sort order column:

Insert a new column, suppose its to the left of your data, Column A. In
cell A2 (assuming you have titles on row 1) enter 1 and in cell A3 enter 2.
Select both cells and double-click the fill handle. This will work if there
is data in all the cells in column B (the original column A).

This will create a column of numbers which you can later sort on to return
you to the default order.

 

[Marie Robinson]

Thanks--what a relief to have this info now!

 

[Marie Robinson]

Thank you! By the way I thought I knew what a cell handle was but double
clicking didn't do anything. I think I understand what was intended to
happen.

 

[David Biddulph]

The fill handle is in the bottom right-hand corner of the cell. Select the
cell, then move the cursor over the black square in the bottom right-hand
corner, and the cursor turns to a thin black plus instead of the normal
thick white one.

 

[Marie Robinson]

Thank you David and Shane. Shane's method worked the best. Since the row
numbers were not as a result of a formula, the numbers didn't change to match
the new row numbers caused by the sorting. In other words using the formula
method allowed the new column of row numbers to change depending on their new
position in the sorted chart.

Thanks again everyone!

 

[David Biddulph]

If Dave Peterson's method didn't work for you, it's presumably because you
missed out the following step from his recommendation:
"convert those formulas to values
Select the range, then edit|copy followed by edit|paste special|values."

 

[Marie Robinson]

I did see those steps. I don't know how to 'convert those formulas to
values' but since the formula changed all by itself to the row number, I
assumed that step was automatic. I also didn't know what the second line in
the instruction meant. Will you translate those two steps?

 

[Dave Peterson]

These were the instructions...

Insert a new column A.
Fill each cell in that column with a formula
=row()
convert those formulas to values
Select the range, then edit|copy followed by edit|paste special|values.

Then sort the data as much as you want (include all the columns).
Make your changes.
And resort the data by that helper column.
Then delete the helper column.

======================

To convert a cell (or a range of cells) that contains a formula to value(s):
you can select that cell/range,
then use Edit|copy
followed by
Edit|paste special|Values
(Edit is on the worksheet menubar at the top of the screen in excel versions
before xl2007).

If the second line is this:
Fill each cell in that column with a formula
=row()

You can select the range to fill with a formula and then type:
=row()
and hit control enter to fill all the cells in that selected range with that
formula.

There are other ways, too.

Debra Dalgleish shows how to put the formula (or value) in a cell and use the
autofill button here:
http://contextures.com/xlDataEntry01.html#Mouse

 

[Marie Robinson]

Dave I understand a little better. I think my Excel 2007 doesn't have the
feature of:

Edit|paste special|Values

I really appreciate the contextures URL!

 

[Dave Peterson]

You don't have the worksheet menu bar at the top, but you do have
Paste special|values.

You can rightclick on the destination cell and choose it from the popup. Or use
the icon on the ribbon (Look again and you'll find it).

 

[Marie Robinson]

Hi Dave. Regarding this sorting query. I made a column of numbers as text,
starting with the header row as row 1 and then the rows following in
numerical order, following the previous instructions.

For some reason now when I sort the chart to make edits and then I want to
sort the chart back in order based on the column of numbers, it's sorting all
numbers beginning with 1 for example:

1, 10, 100, 1000, 1001, 1002, etc. I'm using the Data, Sort, and choosing
the column with the column heading of 1, sorting A-Z order. This worked for
me in the past. I can't figure out what I did wrong.

Marie.

 

[Dave Peterson]

I'm betting that you either made the list using real numbers (not text).

Or you're using xl2002 (or higher) and got a warning message that said:
The following sort key may not sort as expected because it contains some numbers
formatted as text:

And you chose:
Sort anything that looks like a number, as a number

Hi Dave. Regarding this sorting query. I made a column of numbers as text,
starting with the header row as row 1 and then the rows following in
numerical order, following the previous instructions.

For some reason now when I sort the chart to make edits and then I want to
sort the chart back in order based on the column of numbers, it's sorting all
numbers beginning with 1 for example:

1, 10, 100, 1000, 1001, 1002, etc. I'm using the Data, Sort, and choosing
the column with the column heading of 1, sorting A-Z order. This worked for
me in the past. I can't figure out what I did wrong.

Marie.

 

[Marie Robinson]

You betcha I did.

--
Marie

I'm betting that you either made the list using real numbers (not text).

Or you're using xl2002 (or higher) and got a warning message that said:
The following sort key may not sort as expected because it contains some numbers
formatted as text:

And you chose:
Sort anything that looks like a number, as a number

 

[Marie Robinson]

Although when I look at the cell properties it does show the numbers as text.
I use 2000 at home and 2003 here at work.

 

[Marie Robinson]

Dave, are you saying that the column has to be in text format to be able to
sort the whole database based on that one column of numbers--in other words
the sort result will no longer be 1, 10, 100, 1000, 1001 but 1, 2, 3, 4,
etc.? No matter if I have the column chosen to be text or general format,
the sorting result is the same--not how I want it. Thanks for your patience.

 

[Dave Peterson]

If your data is text and you sort as text, then you'll see:

1
100
111
1111
2
212
33333
4
445
....

But in xl2002+, you can specify that text that looks like numbers should be
sorted with the numbers.

And remember that if you only change the format of the cell, it doesn't change
the underlying value in the cell. You'll have to do something more to convert
the text numbers to number numbers.

If you select an empty cell
edit|copy
select the text number range
edit|Paste special|multiply and values

You can coerce the text numbers to number numbers.

There are other ways, too.

Dave, are you saying that the column has to be in text format to be able to
sort the whole database based on that one column of numbers--in other words
the sort result will no longer be 1, 10, 100, 1000, 1001 but 1, 2, 3, 4,
etc.? No matter if I have the column chosen to be text or general format,
the sorting result is the same--not how I want it. Thanks for your patience.

 

[Marie Robinson]

I have xl2000 here at home. I have xl2003 at work. I work on this project
at work, email it home and save over what's here at home. Sometimes I would
like to make edits at home that would be easier to make if I could sort it by
certain columns and then put it back in the original order. Does xl2000 not
have the same ability as you describe below? Is switching back and forth
between 2000 and 2003 the problem?

 

[Marie Robinson]

Dave, thanks for your patience. I have finally figured out the problem and
now know how to convert to numbers!