Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

MS Office - Excel

Jun 25, 2005 10:53PM PDT

I have Win 98 SE, as OS. But I have installed MS Office
XP in addition to that. Now I have a problem and I need a tip or trick to make it easy/comfortable.
I am feeding data of my friends, relatives, colleagues,and all the sundry about their telephone numbers, mailing addresses, birthdays,etc.,etc.,
I have already fed data of about 100 people. I maintained the alphabatical order, in order to locate them easily.Now I got a new member whose name I have to insert in between, in order to maintain alphabatical order. Inbetween insertion of the name requires alteration of the subsequent serial numbers, which are around 80 in number. I know how to do it. But it is very laborious and time consuming.
Is there any trick or tip, so that the subsequent numbers will raise by one. OR any trick, so that I type the data and press ENTER, it will automatically go to the appropriate place.
Please some of you respopnd.

Discussion is locked

- Collapse -
A formula instead?
Jun 26, 2005 1:04AM PDT

Maybe the serial number should be formulaic instead of a fixed number?

- Collapse -
serial number issue
Jul 1, 2005 4:03AM PDT

I might add that if we were dealing with products, as opposed to people, we might see the need for this serial number. Often a product will have a serial number regardless of where it is sorted in the worksheet. So, perhaps the solution is this: if you want Uncle Ted to always be #33 for some reason known to you, just ignore the out of order numbers when you add in new people.

I cannot think of any advantage to this, but what is one more column gonna hurt! Peace.

- Collapse -
I don't see any need ...
Jun 26, 2005 5:06AM PDT

to give them a serial number, in fact. That would be the easiest way to solve this problem. What's the use of it, if it changes with every addition?

If you really prefer to give them a changing serial number, I can't see anything 'laborious and time consuming' in using either the fill handle (http://archive.baarns.com/excel/faq/xsc.asp) to fill a range by dragging or Edit>Fill>Series to fill a range by entering a start and stop value in a screen. It's so little work it's not worth the trouble to write a macro for it, in my opinion.

Kees

- Collapse -
Serializing data entries
Jun 26, 2005 1:36PM PDT

Both Bob and Kees have valid suggestions. The choice depends mainly in how often you make additions, and if you make several new entries at one time. Yes, a macro could be prepared to let you type in a name and hit enter to do all you wish. But, as Kees said, it probably wouldn?t be worth the trouble unless you are making massive additions.

If you add about three or more new names at once, I think adding them at the bottom, and then sorting alphabetically by name might be best. If you add just one or two at a time, then inserting a new row at the appropriate position is easiest (I gather this is what you now do.) This has the drawback that it is easy to insert at the wrong place (I sometimes do);but of course, you can always use sort to correct such an error.

The type of serialization is not clear. If you are serializing them alphabetically, then I would repeat Kees? question ? why? If you should need such a serial number occasionally, you can easily derive it from the line number. But if you do want it, setting it by means of a formula as Bob suggested, is a trivial matter. After entering the new data, in the column where you store this serial number, simply enter
=Row()-offset
where offset is the number of the first data row minus one. This serial number will always be consistent with the alphabetic order, no matter what new entries are made, nor how. Alternatively, you can use the fill method suggested by Kees; this could be better if making many new entries at one time.

If you are serializing by order of entry (latest entry has highest number regardless of alphabetic position), then search for the maximum in the previous entries, and add one. Most other serializations, such as by address, birthday, or whatever, can be more complex, and a macro might then be the best solution.

Hope this helps some.

Frank

- Collapse -
MS Office - Excel....
Jun 29, 2005 9:47PM PDT

Thanks to all those who have responded. I will try the advises and let you know if I face any difficulty.
THANK YOU ALL ONCE AGAIN.

Blueboy888