(Primary) Key Column?

[Rebecca]

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.

 

[JP]

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

 

[Rebecca]

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.

 

[Rebecca]

I apologize for being rude and impatient. I deeply appreciate all the help
available on this MS Discussion Group.

 

[JP]

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