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.