Custom sorting *not* using a custom list

[colinlee]

I've tried to use the custom list function to sort data using Excel 2007, but it's apparently limited to a list of 255 items whereas I have about 3000.

Can anyone help me with macro code to sort a selection based on the order of a list in a separate workbook/worksheet? (list will be stored in my personal macro workbook)

Thanks in advance...

 

[shg]

You could have a list of values in sort order someplace, and in a separate column, use a MATCH formula to retrieve the position of the sort value in the list. Then sort by that column.

 

[colinlee]

Hmmmm... that's an interesting idea. I created a new column and used a vlookup formula to assign a number to each name in the list:

=VLOOKUP(A2,'[Macro library.xls]LIST09'!A$2:H$2153,8,FALSE)

Not sure how this could be done with a Match formula.

Anyone have a macro solution?

Thanks shg4421!

 

[shg]

Not sure how this could be done with a Match formula.

If you have a list of the keys in the desired sort order, then

=MATCH(rowkey, keylist, 0)

returns the sort value.

 

[colinlee]

OK. I looked up some information about how to use it... it's always neat to learn to use a new tool! Thanks.