Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CRUCIAL: Creating a Custom list 40,000+ lines????
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 -- Thanks a ton!,:) ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
CRUCIAL: Creating a Custom list 40,000+ lines????
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 ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
CRUCIAL: Creating a Custom list 40,000+ lines????
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 wrote: 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 ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
CRUCIAL: Creating a Custom list 40,000+ lines????
Dave,
thanks for a response. What do you mean by Calculate? Are you referring to a Excel function? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
CRUCIAL: Creating a Custom list 40,000+ lines????
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 iamhumble wrote: Dave, thanks for a response. What do you mean by Calculate? Are you referring to a Excel function? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding custom list and text boxes to the custom tool bar from Excel C API | Excel Discussion (Misc queries) | |||
creating a custom list of numbers | Excel Worksheet Functions | |||
Custom lines on xy plot | Charts and Charting in Excel | |||
Creating custom list with a comma in it | Excel Discussion (Misc queries) | |||
Creating a graph with 3 lines | New Users to Excel |