How can I sort an entire spreadsheet from a list

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.

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.

"(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.

Maybe you don't mean "sort" in Excel's sense of the word.

If you just want to identify the old items from column A, use the first
formula I suggested, do the sort on columns A and B, then delete columns B and
C and skip the rest of it.

Or you could use this formula in the new column B (which does the same thing,
but with a different function):

=IF(COUNTIF($D$2:$D$500,A2)0,0,1)

again, sort columns A and B by column B, then delete columns B and C.


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.

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.