I'm still not sure whether you want to sort the "carried" product list. If you
do, you should be able to just do that by selecting columns B and to the
right, use Data/Sort and do not expand the selection to include column A.


On Tue, 16 Nov 2004 19:30:02 -0800, "prod sorter"
wrote:

"(I have assumed that there are no items in
the original column B that have no match in A)"

In fact, and I'm sorry I did not make this clear, Column B will always have
more items than the sort list in Column A.

Example: Column A contains product SKUs that mostly are contained in column
B with all supporting data in the columns that follow (hence why all columns
past Column B must be sorted with B). Any Item in column A that does not
match an Item in Column B, consttutes a discontinued product and that number
must be moved to the end of Column A for easy identification. In essence, I
wish to pick the products out that are carried and identify those that have
been discontinued.



"Myrna Larson" wrote:

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.