Yesterday we talked about sorting data. We covered what to do in case your list gets scrambled, to try to get it back in good order.
Here is another tip suggested by Tom Lieven. Here is what he wrote:
"I like your idea for named ranges for making sure your data doesn't get screwed up. The other backup/restore options also work. I've developed another one that is only slightly more time consuming than the ones you mentioned and gives me an immediate way to recover your data. When I build a large sortable database I make a special column called "Original Sequence". Anytime I want to put the data back in its original input sequence, I just sort by the "Original Sequence" field. This provides me with some audit capability as well as an immediate recovery. It may be overkill, but it does work. It's an idea."
( we agree 100 percent- perhaps a minor tweak- to keep the names short, you could call the field "Index")
This follows good database practices. If you use Access at all, for example, you will notice that it will ask for a key index field.
In Excel, you could also set up an index field in your list, so each row has a unique identifier. This index field can be a home base for you to return to, by doing your first sort on the index field.
In some cases you may already have an index field. The question to ask- "Is there a field that has a unique value for each record"
For example, if you have a list of invoices, you probably already have such a field with the invoice number. That in essence is your index field, so there is no need to create a separate record.
Creating an index field can be simple. Insert a column. Put 1 in the first record and drag down to create a unique number for each row. Make sure your named range has been updated to include the column with the index field.
How not to do it? By making the index field a numerical formula. Suppose you set the numerical index on each row to be one more than the row above- like "=+A2+1". What happens is that your index field "re-indexes" with every sort. Row 3 will always have an index of 2, row 4 always 3, etc., regardless of the other fields in the row. In other words, you corrupt the index field.
As an alternative to straight numbers (1,2,3 etc) you might also come up with some other meaningful indexes. Sometimes it can be a combination of values from two fields. While each field is not unique, the combination of the two might be.
But except for special circumstances, we suggest keeping it simple and just using numbers.
Besides protecting you, the index field can also save you time. it can be a quick way to get back to your original sort of the list. You only have to sort on the index field, rather than using a combination of sorting conditions.
In sum, when you have a big list, make sure you have an index field for fast and safe sorting.