Sorting like MS Access

[Xelpme]

Is there a way to sort data in Excel like Access, that sorts data against some other data rather than alphabetically or numerically?

For instance, let us say we have a column A which has the following extracted from another spreadsheet that must remain in this order:

1DF
1GF
3GD
33ES
etc

Then I import some data into the spreadsheet into column B and C, and it comes in a random order as follows:

1GF----2
33ES---7
1DF----4
3GD----1

Now, I want to sort this data against column A so the result is as follows:

1DF----4
1GF----2
3GD----1
33ES---7


At the moment I have to carry out a long process that involves the following steps:

(1). Copy Part Numbers only from a spreadsheet that are based on a specific order and paste into a new spreadsheet.

(2). Import random data that contains the same and more part numbers as in (1) and other data.

(3). Import both of these tables into Access.

(4). Create a query that links the two tables based upon part number. This sorts the imported data against (1)

(5). Copy and paste this sorted data into a spreadsheet.


Also, is there a quicker way to sort alphanumeric data than:

1. Calculating the length of the data.
2. Sorting by the length.
3. Removing any letters at the end of the data to leave only a letter(s) followed by numbers.
4. Repeat 1 and 2.
5. Data > Sort by column (length of text) and column (text)

[boylejob]

Xelpme,

I have been pondering your question all day. I do not know of a feature in Excel that will allow you to sort the way you are wanting. That certainly does not mean that it does not exist because I definitely do not know everything about Excel.

Would it be possible to approach your problem in another way. Lets think about using two sheets in a workbook. On the first sheet you do step #1 and load the intial batch of part number. You then take the data from step #2 and put it on sheet #2.

The last step of this process would be to use VBA code to get data from sheet 2 over to sheet 1. You would basically get the part number from sheet 2, search for that part number on sheet 1, and if it is found move part number and quanity??? from sheet 2 to columns B and C on sheet 1. If the part number is not found, you would do ????.

This could be done very quickly. I recently had a some code that was doing something simlar and it processed 1500 records in less than 4 seconds.

What do you think? Would approaching your problem from this angle help?