So that I am clear on what you want:

You need to re-order column A AND the B and columns to its right, but not via
one sort.

You want the data in column A to act as a custom sort order, with the
exception that any items in A that aren't in B should be moved to the bottom.

Let's say you have 600 records in column A and 500 in column B.

To take care of re-ordering column A:

I would insert 2 new columns, B and C. Put this formula in B2

=IF(ISNA(MATCH(A2,$D2:$D500,0)),1,0)

and copy down through B600. Now sort columns A and B on column B. The items
from A that have no matches in the original column B will be at the bottom of
A; the values that do have matches will be in their original order at the top.

Then clear column B.

Now put a formula in column C

=MATCH(D2,$A$2:$A$600,0)

and copy that down through C500. (I have assumed that there are no items in
the original column B that have no match in A). Now sort columns C and the
columns to its right, by column C. Do not include columns A and B in the sort.
Then delete columns B and C.


On Tue, 16 Nov 2004 18:06:30 -0800, "prod sorter" <prod
wrote:

I would like to sort a spreadsheet using column B by a list found in column A
that contains mostly matches to column B. I would like for columns B and up
to be sorted using the list in A. If a cell value in A does not match any in
column B, I want that value moved to the end of column A.

I use the custom list option to achieve this right now, but it is very
cumbersome and limited.