Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#19
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#20
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#21
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i remove sort and see the original order of the data? | Excel Discussion (Misc queries) | |||
how to restore the original data order | Excel Discussion (Misc queries) | |||
trace order of data entry | New Users to Excel | |||
Sort Data Into Numerical Order..! | Excel Discussion (Misc queries) | |||
Sort data into order of product | Excel Discussion (Misc queries) |