Maintaining a numbered list: how to sort and modify

Hi. I am new to MS Excel, and use the MS Office Excel 2007 version. I am also new to this forum, and this is my first post.

Please help me understand how I can use Excel to perform the last step of "REORDER" in the following problem description. I need to be able to maintain a numbered list where I can keep entries in the order they were entered (original sequence order), and identify them by the number of their position number in the list. The list has a maximum of 999 entries in it. I also need to be able to sort the list which is in original sequence order, into a different "sort-order". SO FAR, I CAN DO THIS up to the REORDER step shown below.

After sorting the list into a new "sort-order", I need to be able to delete an entry from the list while the list is still in "sort-order", then REORDER the list of the remaining entries (all entries except the deleted entry) into the same original sequence order but with no gap in numbering for the deleted entry. An example follows, showing the actions of (1) Sorting, (2) Deleting an item, and (3) Reordering the list to original sequence order:

ORIGINAL SEQUENCE ORDER

NO__ITEM
1-----Green Circle A
2-----Green Square B
3-----Red Square C
4-----Blue Triangle D
5-----Green Square E
6-----Red Square F
7-----Blue Circle G
8-----Green Circle H
9-----Blue Triangle I
10----Red Circle J

STEP 1: SORT INTO SORT-ORDER

NO__ITEM
4-----Blue Triangle D
7-----Blue Circle G
9-----Blue Triangle I
1-----Green Circle A
2-----Green Square B
5-----Green Square E ......... STEP 2: DELETE THIS ITEM #5
8-----Green Circle H
3-----Red Square C
6-----Red Square F
10----Red Circle J

STEP 3: REORDER BACK TO ORIGINAL SEQUENCE ORDER

NO__ITEM
1-----Green Circle A
2-----Green Square B
3-----Red Square C
4-----Blue Triangle D
5-----Red Square F
6-----Blue Circle G
7-----Green Circle H
8-----Blue Triangle I
9-----Red Circle J

The step (3) I call "REORDER" above puts items back in the original sequence order with the deleted item 5 removed, and items numbered from 6 to the end of the list renumbered to show their new position in the original sequence order with no gaps in numbering scheme.

This is similar to many inventory management problems where there is a limited number of locations for inventory, and a location identifier is assigned to the item based on arrival order, a list must be sorted to find needed items at which time these items are removed from inventory, but the list must continue to reflect arrival sequence order so new items can be added to the end of the list. Another way of looking at this problem is that excel is being used like a database for inventory in a very limited way.

I think this must be simple to do, but am too unfamiliar with Excel to know the tools available.
Your help will be greatly appreciated. Thanks in advance.

Unless you enter the "ID" manually, the only other way I can think of would be to use VBA. If you use a formula, it will change when you sort, and will get messed up if you delete a row

Hello and welcome

The simplest way is to number the items with a formula. So assuming item 1 is in A2 enter

Formula:

Please Login or Register  to view this content.

then copy this down.

Richard, that will change if sorted?
(or not change, depending on how you look at it)

FDibbins, Thank you for your answer. I believe I am newer to excel than you think. I researched VBA to find out what it is and from what I can understand, this allows formulas in a Basic program to apply to cells. I couldn't figure out how to use this to help with my problem.

I do need your help to learn how to SECURE cells in excel (if this can be done) - - I will define what I mean by SECURE below...
I think the workaround for this problem involves having two columns with different sort keys in each column in addition to the data column. When I do the sort, I need to sort the column with data and the column with Key 1, while keeping the column with Key 2 unsorted. And I need to delete the cells in a row such that the data and Key 1 cells in the row are deleted while keeping Key 2 column cells unchanged.

I don't know the correct word to use, so I will define my own word which I will call "SECURED". I define SECURED to mean that when a cell or column or row is SECURED, that the values in each cell can not change until the cell is UNSECURED, so the contents of a cell can not be modified, or deleted, or moved in a sort. (I think excel uses the words LOCKED and PROTECTED, and I think they mean something else.) I want to be able to SECURE the column with Key 2 (so no sorting or deletion of rows in the sheet will effect the contents of this column since it is SECURED), and then I need to sort the columns with data and Key 1 (these rows will allow the change in position of both data and Key 1 since they are UNSECURED)

SO MY NEW QUESTION IS: Is there a way to SECURE and UNSECURE cells in excel 2007?

PS - You were correct, and Richard's suggestion was not the fix - but his effort is appreciated, as is yours.

Hi Ford,

Yes , quite correct. I completely missed the sorting aspect.

@Mark. I'm still not quite clear in what you're asking. The original post seemed to only require the original entries, minus any deletions to be listed in a strict numerical order with no sequential gaps after the deletion/resort. i.e. The entries that preceded the cell/row that is deleted would still carry the same numeric identifier, but after the sort the items left would carry a numeric identifier one less than they had before.

Your last explanation seems to imply some different concept, that of protecting stuff from changes and only allowing changes in a controlled situation. You don't mention the business of keeping a strict numerical identifier with no gaps.

Can you clarify please?

Mark, I think it might help if you can upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like - perhaps include a before and after, and show what your "secured" area would be, and whatthat should - and should not - be able to do?

This should answer both FDibbins (Ford’s) and Richard’s question.
I keep emphasizing, I am new to excel. I'm not new to programming logic, but I don’t know what can be done in excel or the best way to do it … or even excel commands. So, as I research the problem, I learn a little more of what excel can do, and can see another way forward. I now believe I can do the ‘sort and modify’ operation I need by using two separate keys associated with the data. Each key would be kept in a separate column (a column for Key1 and a column for Key2 in addition to the column for Data). Here is the example Ford asks for (the way this forum presents information requires use of dashes as – instead of just using spaces if you want to keep things aligned and looking like columns, there is no other significance to the use of dashes in this example):

ORIGINAL SEQUENCE ORDER

KEY2-------KEY1-----DATA
1-----------1----------Green Circle A
2-----------2----------Green Square B
3-----------3----------Red Square C
4-----------4----------Blue Triangle D
5-----------5----------Green Square E
6-----------6----------Red Square F
7-----------7----------Blue Circle G
8-----------8----------Green Circle H
9-----------9----------Blue Triangle I
10---------10---------Red Circle J

STEP 1: SORT DATA AND KEY1 using DATA in ascending order (keep KEY2 unchanged or “SECURED” as I have called it):

KEY2-------KEY1-----DATA
1-----------4----------Blue Triangle D
2-----------7----------Blue Circle G
3-----------9----------Blue Triangle I
4-----------1----------Green Circle A
5-----------2----------Green Square B
6-----------5----------Green Square E
7-----------8----------Green Circle H
8-----------3----------Red Square C
9-----------6----------Red Square F
10---------10---------Red Circle J

STEP 2: DELETE ROW(S) WITH DATA I WILL CHOOSE MANUALLY. For this example I choose all Green Squares, and remove them. Entries shift up to fill the gap created for KEY1 and DATA but nothing changes for KEY2 which is SECURED.

KEY2-------KEY1-----DATA
1-----------4----------Blue Triangle D
2-----------7----------Blue Circle G
3-----------9----------Blue Triangle I
4-----------1----------Green Circle A
5-----------8----------Green Circle H
6-----------3----------Red Square C
7-----------6----------Red Square F
8-----------10---------Red Circle J
9
10

STEP 3: SORT DATA AND KEY1 using KEY1 in ascending order (keep KEY2 unchanged or “SECURED” as I have called it):

KEY2-------KEY1-----DATA
1-----------1----------Green Circle A
2-----------3----------Red Square C
3-----------4----------Blue Triangle D
4-----------6----------Red Square F
5-----------7----------Blue Circle G
6-----------8----------Green Circle H
7-----------9----------Blue Triangle I
8-----------10---------Red Circle J
9
10

STEP 4: REPLACE KEY1 WITH KEY2

KEY2-------KEY1-----DATA
1-----------1----------Green Circle A
2-----------2----------Red Square C
3-----------3----------Blue Triangle D
4-----------4----------Red Square F
5-----------5----------Blue Circle G
6-----------6----------Green Circle H
7-----------7----------Blue Triangle I
8-----------8----------Red Circle J
9-----------9
10---------10

STEP 5: ADD NEW DATA (there are 10 slots in my example, and 999 in the real problem)

KEY2-------KEY1-----DATA
1-----------1----------Green Circle A
2-----------2----------Red Square C
3-----------3----------Blue Triangle D
4-----------4----------Red Square F
5-----------5----------Blue Circle G
6-----------6----------Green Circle H
7-----------7----------Blue Triangle I
8-----------8----------Red Circle J
9-----------9----------Red Triangle K
10---------10---------Green Triangle L

NOW READY FOR A NEW ‘SORT AND MODIFY’ OPERATION … rinse and repeat, as desired.

That is why I asked …
“SO MY NEW QUESTION IS: Is there a way to SECURE and UNSECURE cells in excel 2007?”

Even if you see another way to accomplish this operation, please tell me if there is a way to make a column impervious to change (i.e. – SECURED) in excel 2007. Thanks in advance.