Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
(Primary) Key Column?
Greetings. I am using MS VISTA and Excel 2007 (I am a relative newcomer).
I was wondering if I can create a (Primary) Key column (or whatever it's called), so that I can resort the spreadsheet back to the original order / sequence (that is, after I make some revisions / updates in certain cells). The problem arises when I have to insert new rows, which create gaps in the column I am currently using (it is simply a column with a numbered sequence 1-about 700000!). How can this be done? And please explain in very simple English. Thank you. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
(Primary) Key Column?
Just curious, why do you need to sort the spreadsheet before making
updates? Can't you make the updates without sorting it first? Then you wouldn't have the problem of needing to sort it back to the way it was. If you want to preserve a list of data so you can sort it and then re- sort it back to its original sort order, just insert a helper column (I use column A) and fill down the number series (1,2,3 ....). Sort by whatver column you want, then when you are done, just sort it by that column again. Why are you inserting rows, I thought you wanted to preserve the original sort order. Can't you just append the new data to the bottom? Otherwise, just insert your rows, then recreate the number series. HTH, JP On Sep 21, 12:34*am, Rebecca wrote: Greetings. I am using MS VISTA and Excel 2007 (I am a relative newcomer). I was wondering if I can create a (Primary) Key column (or whatever it's called), so that I can resort the spreadsheet back to the original order / sequence (that is, after I make some revisions / updates in certain cells). The problem arises when I have to insert new rows, which create gaps in the column I am currently using (it is simply a column with a numbered sequence 1-about 700000!). *How can this be done? And please explain in very simple English. Thank you. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
(Primary) Key Column?
Thanks, JP, but ...
First, I am well aware of the "helper column" procedure you mentioned, but this is not what I am after. Regarding your questions, "Why are you inserting rows," etc., well, now I will have to spend time explaining. With all due respect, I wish the "experts" would just provide answers and not ask why, but here goes. I am using Excel more or less as a database. Yes, I know this is an unorthodox, blasphemous, inefficient, whatever, but please spare the lectures. It works just fine, and better with MS's bulky Access, which I used to use, but it didn't come with my Office 2007 package. Now occasionally I have to insert and/or delete a row or rows, which will disrupt the sequence of rows top to bottom (don't ask why -- please). So I am left with a blank or blanks (or deletions) in the so-called "helper row" you mentioned above. Access has a (Primary) Key, if I remember, and I was wondering if I could set up a col. that would automatically recalculate the sequence if a row was added or subtracted. Then after various sorts and the like were performed, I could resort on the "helper key" column to return to the original sequence (though revised somewhat depending on whether or not new rows have been added or subtracted). As you can see, it would have been better just to provide an answer to my original question, even if my use of Excel is utterly unorthodox. All I wanted to know is how to set up a solid column that will renumber itself as rows are added and/or subtracted. But thanks for your imput, anyway. "JP" wrote: Just curious, why do you need to sort the spreadsheet before making updates? Can't you make the updates without sorting it first? Then you wouldn't have the problem of needing to sort it back to the way it was. If you want to preserve a list of data so you can sort it and then re- sort it back to its original sort order, just insert a helper column (I use column A) and fill down the number series (1,2,3 ....). Sort by whatver column you want, then when you are done, just sort it by that column again. Why are you inserting rows, I thought you wanted to preserve the original sort order. Can't you just append the new data to the bottom? Otherwise, just insert your rows, then recreate the number series. HTH, JP On Sep 21, 12:34 am, Rebecca wrote: Greetings. I am using MS VISTA and Excel 2007 (I am a relative newcomer). I was wondering if I can create a (Primary) Key column (or whatever it's called), so that I can resort the spreadsheet back to the original order / sequence (that is, after I make some revisions / updates in certain cells). The problem arises when I have to insert new rows, which create gaps in the column I am currently using (it is simply a column with a numbered sequence 1-about 700000!). How can this be done? And please explain in very simple English. Thank you. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
(Primary) Key Column?
The reason why JP asked the question he did is that in a large proportion of
the questions asked on this group, the question asked is either not what the OP *intended* to ask, or is such an incomplete question that an appropriate answer can't be given. Most OPs are happy to provide the extra information to help the "experts" to whom you refer to provide an answer (and please remember that the experts are doing so in their own time, without payment, and out of a desire to help the rest of the Excel community). The *easiest* thing for JP to have done would have been merely to have ignored your original message, or to have said "No" to your question. He was, however, trying to be helpful. He, and others, will have seen the attitude you demonstrated to those trying to help you. -- David Biddulph "Rebecca" wrote in message ... Thanks, JP, but ... First, I am well aware of the "helper column" procedure you mentioned, but this is not what I am after. Regarding your questions, "Why are you inserting rows," etc., well, now I will have to spend time explaining. With all due respect, I wish the "experts" would just provide answers and not ask why, but here goes. I am using Excel more or less as a database. Yes, I know this is an unorthodox, blasphemous, inefficient, whatever, but please spare the lectures. It works just fine, and better with MS's bulky Access, which I used to use, but it didn't come with my Office 2007 package. Now occasionally I have to insert and/or delete a row or rows, which will disrupt the sequence of rows top to bottom (don't ask why -- please). So I am left with a blank or blanks (or deletions) in the so-called "helper row" you mentioned above. Access has a (Primary) Key, if I remember, and I was wondering if I could set up a col. that would automatically recalculate the sequence if a row was added or subtracted. Then after various sorts and the like were performed, I could resort on the "helper key" column to return to the original sequence (though revised somewhat depending on whether or not new rows have been added or subtracted). As you can see, it would have been better just to provide an answer to my original question, even if my use of Excel is utterly unorthodox. All I wanted to know is how to set up a solid column that will renumber itself as rows are added and/or subtracted. But thanks for your imput, anyway. "JP" wrote: Just curious, why do you need to sort the spreadsheet before making updates? Can't you make the updates without sorting it first? Then you wouldn't have the problem of needing to sort it back to the way it was. If you want to preserve a list of data so you can sort it and then re- sort it back to its original sort order, just insert a helper column (I use column A) and fill down the number series (1,2,3 ....). Sort by whatver column you want, then when you are done, just sort it by that column again. Why are you inserting rows, I thought you wanted to preserve the original sort order. Can't you just append the new data to the bottom? Otherwise, just insert your rows, then recreate the number series. HTH, JP On Sep 21, 12:34 am, Rebecca wrote: Greetings. I am using MS VISTA and Excel 2007 (I am a relative newcomer). I was wondering if I can create a (Primary) Key column (or whatever it's called), so that I can resort the spreadsheet back to the original order / sequence (that is, after I make some revisions / updates in certain cells). The problem arises when I have to insert new rows, which create gaps in the column I am currently using (it is simply a column with a numbered sequence 1-about 700000!). How can this be done? And please explain in very simple English. Thank you. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
(Primary) Key Column?
I apologize for being rude and impatient. I deeply appreciate all the help
available on this MS Discussion Group. "David Biddulph" wrote: The reason why JP asked the question he did is that in a large proportion of the questions asked on this group, the question asked is either not what the OP *intended* to ask, or is such an incomplete question that an appropriate answer can't be given. Most OPs are happy to provide the extra information to help the "experts" to whom you refer to provide an answer (and please remember that the experts are doing so in their own time, without payment, and out of a desire to help the rest of the Excel community). The *easiest* thing for JP to have done would have been merely to have ignored your original message, or to have said "No" to your question. He was, however, trying to be helpful. He, and others, will have seen the attitude you demonstrated to those trying to help you. -- David Biddulph "Rebecca" wrote in message ... Thanks, JP, but ... First, I am well aware of the "helper column" procedure you mentioned, but this is not what I am after. Regarding your questions, "Why are you inserting rows," etc., well, now I will have to spend time explaining. With all due respect, I wish the "experts" would just provide answers and not ask why, but here goes. I am using Excel more or less as a database. Yes, I know this is an unorthodox, blasphemous, inefficient, whatever, but please spare the lectures. It works just fine, and better with MS's bulky Access, which I used to use, but it didn't come with my Office 2007 package. Now occasionally I have to insert and/or delete a row or rows, which will disrupt the sequence of rows top to bottom (don't ask why -- please). So I am left with a blank or blanks (or deletions) in the so-called "helper row" you mentioned above. Access has a (Primary) Key, if I remember, and I was wondering if I could set up a col. that would automatically recalculate the sequence if a row was added or subtracted. Then after various sorts and the like were performed, I could resort on the "helper key" column to return to the original sequence (though revised somewhat depending on whether or not new rows have been added or subtracted). As you can see, it would have been better just to provide an answer to my original question, even if my use of Excel is utterly unorthodox. All I wanted to know is how to set up a solid column that will renumber itself as rows are added and/or subtracted. But thanks for your imput, anyway. "JP" wrote: Just curious, why do you need to sort the spreadsheet before making updates? Can't you make the updates without sorting it first? Then you wouldn't have the problem of needing to sort it back to the way it was. If you want to preserve a list of data so you can sort it and then re- sort it back to its original sort order, just insert a helper column (I use column A) and fill down the number series (1,2,3 ....). Sort by whatver column you want, then when you are done, just sort it by that column again. Why are you inserting rows, I thought you wanted to preserve the original sort order. Can't you just append the new data to the bottom? Otherwise, just insert your rows, then recreate the number series. HTH, JP On Sep 21, 12:34 am, Rebecca wrote: Greetings. I am using MS VISTA and Excel 2007 (I am a relative newcomer). I was wondering if I can create a (Primary) Key column (or whatever it's called), so that I can resort the spreadsheet back to the original order / sequence (that is, after I make some revisions / updates in certain cells). The problem arises when I have to insert new rows, which create gaps in the column I am currently using (it is simply a column with a numbered sequence 1-about 700000!). How can this be done? And please explain in very simple English. Thank you. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
(Primary) Key Column?
Rebecca,
I find that most of the time when I am asked a tech question, the questioner takes a particular path to a goal for granted and only asks for help with the individual step towards that goal that they are stuck on. They assume that this path is the only way to reach the goal. This locks them into a specific solution which may not be the best way to reach their goal. I'm doing you a disservice if I just take your inputs as a given and try to work with them. Instead of just trying to force your way through a particular path, stating your goal and rethinking the problem itself often leads to a much better outcome. There's no autonumber feature in Excel, but here is a recent newsgroup post describing a few ways you can simulate it: http://tinyurl.com/3rdjyz In addition to what is described in that link, you could also write a macro that designates a specific column as a helper column and fills down the numbers for you. After you insert a row, you could just re- run it to re-number the rows. You'd need to hard code the information so that the numbers don't change after you re-sort. And thanks to David for taking time out of his day to assist. --JP On Sep 21, 8:28*am, Rebecca wrote: Thanks, JP, but ... First, I am well aware of the "helper column" procedure you mentioned, but this is not what I am after. Regarding your questions, "Why are you inserting rows," etc., well, now I will have to spend time explaining. With all due respect, I wish the "experts" would just provide answers and not ask why, but here goes. I am using Excel more or less as a database. Yes, I know this is an unorthodox, blasphemous, inefficient, whatever, but please spare the lectures. It works just fine, and better with MS's bulky Access, which I used to use, but it didn't come with my Office 2007 package. Now occasionally I have to insert and/or delete a row or rows, which will disrupt the sequence of rows top to bottom (don't ask why -- please). So I am left with a blank or blanks (or deletions) in the so-called "helper row" you mentioned above. Access has a (Primary) Key, if I remember, and I was wondering if I could set up a col. that would automatically recalculate the sequence if a row was added or subtracted. Then after various sorts and the like were performed, I could resort on the "helper key" column to return to the original sequence (though revised somewhat depending on whether or not new rows have been added or subtracted). As you can see, it would have been better just to provide an answer to my original question, even if my use of Excel is utterly unorthodox. All I wanted to know is how to set up a solid column that will renumber itself as rows are added and/or subtracted. But thanks for your imput, anyway. "JP" wrote: Just curious, why do you need to sort the spreadsheet before making updates? Can't you make the updates without sorting it first? Then you wouldn't have the problem of needing to sort it back to the way it was. If you want to preserve a list of data so you can sort it and then re- sort it back to its original sort order, just insert a helper column (I use column A) and fill down the number series (1,2,3 ....). Sort by whatver column you want, then when you are done, just sort it by that column again. Why are you inserting rows, I thought you wanted to preserve the original sort order. Can't you just append the new data to the bottom? Otherwise, just insert your rows, then recreate the number series. HTH, JP On Sep 21, 12:34 am, Rebecca wrote: Greetings. I am using MS VISTA and Excel 2007 (I am a relative newcomer). I was wondering if I can create a (Primary) Key column (or whatever it's called), so that I can resort the spreadsheet back to the original order / sequence (that is, after I make some revisions / updates in certain cells). The problem arises when I have to insert new rows, which create gaps in the column I am currently using (it is simply a column with a numbered sequence 1-about 700000!). *How can this be done? And please explain in very simple English. Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
two units on primary Y axis | Charts and Charting in Excel | |||
Combining data using a primary key | Excel Discussion (Misc queries) | |||
Primary & Secondary axes to have same value | Charts and Charting in Excel | |||
primary & secondary axis | Charts and Charting in Excel |