How can I sort an entire spreadsheet from a list

[Guest]

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.

 

[Myrna Larson]

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

 

[Guest]

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

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.

 

[Myrna Larson]

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.