Original Sort/Data Entry Order
 

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?
--
Marie

Original Sort/Data Entry Order
 

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.



Marie Robinson wrote:

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?
--
Marie

--

Dave Peterson

Original Sort/Data Entry Order
 

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Marie Robinson" wrote:

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?
--
Marie

Original Sort/Data Entry Order
 

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


"Dave Peterson" wrote:

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.



Marie Robinson wrote:

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?
--
Marie

--

Dave Peterson

Original Sort/Data Entry Order
 

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.
--
Marie


"Shane Devenshire" wrote:

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Marie Robinson" wrote:

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?
--
Marie

Original Sort/Data Entry Order
 

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.
--
David Biddulph

Marie Robinson wrote:
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.

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Marie Robinson" wrote:

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?
--
Marie

Original Sort/Data Entry Order
 

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!
--
Marie


"David Biddulph" wrote:

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.
--
David Biddulph

Marie Robinson wrote:
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.

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Marie Robinson" wrote:

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?
--
Marie

Original Sort/Data Entry Order
 

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."
--
David Biddulph

Marie Robinson wrote:
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!

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.
--
David Biddulph

Marie Robinson wrote:
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.

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Marie Robinson" wrote:

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?
--
Marie

Original Sort/Data Entry Order
 

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?
--
Marie


"David Biddulph" wrote:

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."
--
David Biddulph

Marie Robinson wrote:
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!

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.
--
David Biddulph

Marie Robinson wrote:
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.

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Marie Robinson" wrote:

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?
--
Marie

Original Sort/Data Entry Order
 

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 he
http://contextures.com/xlDataEntry01.html#Mouse


Marie Robinson wrote:

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?
--
Marie

"David Biddulph" wrote:

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."
--
David Biddulph

Marie Robinson wrote:
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!

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.
--
David Biddulph

Marie Robinson wrote:
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.

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Marie Robinson" wrote:

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?
--
Marie




--

Dave Peterson

Original Sort/Data Entry Order
 

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!

--
Marie


"Dave Peterson" wrote:

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 he
http://contextures.com/xlDataEntry01.html#Mouse


Marie Robinson wrote:

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?
--
Marie

"David Biddulph" wrote:

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."
--
David Biddulph

Marie Robinson wrote:
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!

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.
--
David Biddulph

Marie Robinson wrote:
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.

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Marie Robinson" wrote:

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?
--
Marie




--

Dave Peterson

Original Sort/Data Entry Order
 

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 wrote:

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!

--
Marie

"Dave Peterson" wrote:

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 he
http://contextures.com/xlDataEntry01.html#Mouse


Marie Robinson wrote:

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?
--
Marie

"David Biddulph" wrote:

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."
--
David Biddulph

Marie Robinson wrote:
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!

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.
--
David Biddulph

Marie Robinson wrote:
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.

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Marie Robinson" wrote:

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?
--
Marie




--

Dave Peterson


--

Dave Peterson

Original Sort/Data Entry Order
 

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


"Dave Peterson" wrote:

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 wrote:

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!

--
Marie

"Dave Peterson" wrote:

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 he
http://contextures.com/xlDataEntry01.html#Mouse


Marie Robinson wrote:

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?
--
Marie

"David Biddulph" wrote:

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."
--
David Biddulph

Marie Robinson wrote:
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!

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.
--
David Biddulph

Marie Robinson wrote:
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.

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Marie Robinson" wrote:

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?
--
Marie




--

Dave Peterson


--

Dave Peterson

Original Sort/Data Entry Order
 

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 wrote:

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

"Dave Peterson" wrote:

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 wrote:

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!

--
Marie

"Dave Peterson" wrote:

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 he
http://contextures.com/xlDataEntry01.html#Mouse


Marie Robinson wrote:

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?
--
Marie

"David Biddulph" wrote:

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."
--
David Biddulph

Marie Robinson wrote:
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!

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.
--
David Biddulph

Marie Robinson wrote:
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.

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Marie Robinson" wrote:

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?
--
Marie




--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Original Sort/Data Entry Order
 

You betcha I did.

--
Marie


"Dave Peterson" wrote:

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 wrote:

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

"Dave Peterson" wrote:

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 wrote:

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!

--
Marie

"Dave Peterson" wrote:

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 he
http://contextures.com/xlDataEntry01.html#Mouse


Marie Robinson wrote:

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?
--
Marie

"David Biddulph" wrote:

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."
--
David Biddulph

Marie Robinson wrote:
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!

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.
--
David Biddulph

Marie Robinson wrote:
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.

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Marie Robinson" wrote:

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?
--
Marie




--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Original Sort/Data Entry Order
 

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


"Dave Peterson" wrote:

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 wrote:

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

"Dave Peterson" wrote:

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 wrote:

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!

--
Marie

"Dave Peterson" wrote:

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 he
http://contextures.com/xlDataEntry01.html#Mouse


Marie Robinson wrote:

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?
--
Marie

"David Biddulph" wrote:

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."
--
David Biddulph

Marie Robinson wrote:
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!

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.
--
David Biddulph

Marie Robinson wrote:
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.

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Marie Robinson" wrote:

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?
--
Marie




--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Original Sort/Data Entry Order
 

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


"Dave Peterson" wrote:

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 wrote:

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

"Dave Peterson" wrote:

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 wrote:

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!

--
Marie

"Dave Peterson" wrote:

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 he
http://contextures.com/xlDataEntry01.html#Mouse


Marie Robinson wrote:

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?
--
Marie

"David Biddulph" wrote:

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."
--
David Biddulph

Marie Robinson wrote:
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!

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.
--
David Biddulph

Marie Robinson wrote:
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.

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Marie Robinson" wrote:

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?
--
Marie




--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Original Sort/Data Entry Order
 

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.

Marie Robinson wrote:

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

"Dave Peterson" wrote:

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 wrote:

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

"Dave Peterson" wrote:

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 wrote:

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!

--
Marie

"Dave Peterson" wrote:

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 he
http://contextures.com/xlDataEntry01.html#Mouse


Marie Robinson wrote:

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?
--
Marie

"David Biddulph" wrote:

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."
--
David Biddulph

Marie Robinson wrote:
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!

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.
--
David Biddulph

Marie Robinson wrote:
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.

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Marie Robinson" wrote:

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?
--
Marie




--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Original Sort/Data Entry Order
 

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


"Dave Peterson" wrote:

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.

Marie Robinson wrote:

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

"Dave Peterson" wrote:

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 wrote:

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

"Dave Peterson" wrote:

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 wrote:

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!

--
Marie

"Dave Peterson" wrote:

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 he
http://contextures.com/xlDataEntry01.html#Mouse


Marie Robinson wrote:

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?
--
Marie

"David Biddulph" wrote:

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."
--
David Biddulph

Marie Robinson wrote:
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!

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.
--
David Biddulph

Marie Robinson wrote:
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.

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Marie Robinson" wrote:

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?
--
Marie




--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Original Sort/Data Entry Order
 

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


"Dave Peterson" wrote:

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.

Marie Robinson wrote:

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

"Dave Peterson" wrote:

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 wrote:

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

"Dave Peterson" wrote:

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 wrote:

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!

--
Marie

"Dave Peterson" wrote:

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 he
http://contextures.com/xlDataEntry01.html#Mouse


Marie Robinson wrote:

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?
--
Marie

"David Biddulph" wrote:

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."
--
David Biddulph

Marie Robinson wrote:
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!

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.
--
David Biddulph

Marie Robinson wrote:
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.

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Marie Robinson" wrote:

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?
--
Marie




--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Original Sort/Data Entry Order
 

Glad you found a solution.

Marie Robinson wrote:

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

"Dave Peterson" wrote:

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.

Marie Robinson wrote:

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

"Dave Peterson" wrote:

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 wrote:

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

"Dave Peterson" wrote:

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 wrote:

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!

--
Marie

"Dave Peterson" wrote:

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 he
http://contextures.com/xlDataEntry01.html#Mouse


Marie Robinson wrote:

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?
--
Marie

"David Biddulph" wrote:

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."
--
David Biddulph

Marie Robinson wrote:
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!

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.
--
David Biddulph

Marie Robinson wrote:
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.

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Marie Robinson" wrote:

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?
--
Marie




--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson