Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
prod sorter
 
Posts: n/a
Default 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.
  #2   Report Post  
Myrna Larson
 
Posts: n/a
Default

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.


  #3   Report Post  
prod sorter
 
Posts: n/a
Default

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



  #4   Report Post  
Myrna Larson
 
Posts: n/a
Default

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.




  #5   Report Post  
Myrna Larson
 
Posts: n/a
Default

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.




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
In Excel, why sort function fails when applied to a list of date? Excel heavy user Excel Discussion (Misc queries) 1 January 18th 05 07:37 PM
Extracting Values on one list and not another B Schwarz Excel Discussion (Misc queries) 4 January 7th 05 02:48 PM
How do I add a dropdown list in a spreadsheet? sidi New Users to Excel 1 December 29th 04 09:10 PM
How do I sort a birthdate list by month then day regardless of ye. ejeff Excel Discussion (Misc queries) 1 December 11th 04 10:56 PM
How to sort a list correctly? Melani New Users to Excel 1 November 29th 04 04:17 PM


All times are GMT +1. The time now is 02:55 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"