CRUCIAL: Creating a Custom list 40,000+ lines????

[iamhumble]

Hi Everyone ,

Excel XP has an option of sorting using your own list. When I
attempted to import a list of 38,326 lines following these instructions
from the Help file Excel only read in ~150 lines out of my list. Can
the 150 value be overriden somehow?? IF not, does anyone know of a
method/program that allows a user to sort based on a custom key??

--
In a range of cells, enter the values you want to sort by, in the order
you want them, from top to bottom. For example:

Data
High
Medium
Low

Select the range.

On the Tools menu, click Options, and then click the Custom Lists tab.


Click Import, and then click OK.

Select a cell in the list you want to sort.

On the Data menu, click Sort.

In the Sort by box, click the column you want to sort.

Click Options.

Under First key sort order, click the custom list you created. For
example, click High, Medium, Low.

Click OK.

Select any other sort options you want, and then click OK

 

[iamhumble]

What I'm trying to do is this.

I have 3 columns: A , B , C. A is master key column that B needs to be
sorted by and B has duplicates. Every value in B has an unique string
in column C.

EXAMPLE:


A B C
cp-ra-2 cp-ra-2 cph-3
cp-ra-3 cp-ra-3 cph-33
cp-ha-4 cp-m-3 cph-345
cp-za-3 cp-ha-4 cph-53
cp-df-3 cp-ha-4 cph-32

After resorting the output should be this

cp-ra-2 cph-3
cp-ra-3 cph-33
cp-ha-4 cph-53
cp-ha-4 cp-32

 

[Dave Ring]

I think this will work. Make a named range out of your column A that
contains the master keys in the order you want; e.g., name it
"MasterKeys". In cell D1, enter the formula "=MATCH(B1,MasterKeys,0)".
Copy this formula down all the rows in which columns B and C have data.
Calculate, and then sort columns B, C and D by column D.

The MATCH function will convert the keys in column B to numbers
representing their sorted positions in column A. Sorting on those
numbers should produce the order you want.

Dave Ring

 

[iamhumble]

Dave,

thanks for a response. What do you mean by Calculate? Are you
referring to a Excel function?

 

[Dave Ring]

I just mean to be sure the cells in which you entered the formula are
calculated. If you have recalculation on automatic, they will be.

Dave