Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Type question / Sort
Hi
I'm trying to prep some values for a multi-column sort in an array. The array contains something like 300,000 by 10 values I've seen quick sort routines that can sort on a single value, but not perform sorts within the initial sort. My idea was to concatenate all my values into a single value and then sort on it. The problem I'm facing is that my numbers would have to be on the order of ~10E+18-1 long The way the routine works is it looks at the numbers in each column whose length is fixed and never has a situation where there are leading zeros to cause problems. Col 1 Col 2 Col 3 Col 4 Col 5 Col 6 Row x: aaa bbb cc ddddd e fff I only need to arrange based on these 6 columns not the entire 10 in the array My problem is when I create a number like aaabbbccdddddefff it appears to have problems with the "efff" portion. I checked each individual component (which I'm multiplying by some 1nnnn where n is some number of zeros - and they all work fine, its just once I tried to add things. Its like it drops values off. I have the variables defined as Double which I thought would work. Any thoughts? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Type question / Sort
A long can store numbers in this range: 2,147,483,648 to 2,147,483,647
A double can store numbers in this range: -1.79769313486231E308 to -4.94065645841247E-324 for negative values and from 4.94065645841247E-324 to 1.79769313486232E308 for positive values. But only approximately 15 significant digits. -- Regards, Tom Ogilvy "Marston" wrote in message m... Hi I'm trying to prep some values for a multi-column sort in an array. The array contains something like 300,000 by 10 values I've seen quick sort routines that can sort on a single value, but not perform sorts within the initial sort. My idea was to concatenate all my values into a single value and then sort on it. The problem I'm facing is that my numbers would have to be on the order of ~10E+18-1 long The way the routine works is it looks at the numbers in each column whose length is fixed and never has a situation where there are leading zeros to cause problems. Col 1 Col 2 Col 3 Col 4 Col 5 Col 6 Row x: aaa bbb cc ddddd e fff I only need to arrange based on these 6 columns not the entire 10 in the array My problem is when I create a number like aaabbbccdddddefff it appears to have problems with the "efff" portion. I checked each individual component (which I'm multiplying by some 1nnnn where n is some number of zeros - and they all work fine, its just once I tried to add things. Its like it drops values off. I have the variables defined as Double which I thought would work. Any thoughts? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Type question / Sort
I'm trying a similar but slightly different approach now.
Rather than try and turn my values from text to numbers (something caused by our ERP query tool) I'm concatenating the values with &s in the order that I want things sorted and dumping that to a new column in the array then sorting on it. Unfortunately, I think the size of the values being sorted must matter because when I used the quick sort tool that from John Walkenbachs book (similar to the one I got from Alan Bedan) it sorted an array of over 300,000 variables in a little over 10 seconds. But with the array I'm trying to sort, the strings are very long and I suspect that is slowing the sort down. I left it running at work with a timer on it and will see how long it takes to run, hopefully not some ridiculous amount. On the good side though - it does provide a way for sorting across multiple columns. One thought though - my original array was 350,000 x 10 - but the sorting value uses 6 of the 10 values. Would it be faster to drop those 6 (plus the new one) to end up sorting on a single column within a 350,000 x 5 array? "Tom Ogilvy" wrote in message ... A long can store numbers in this range: 2,147,483,648 to 2,147,483,647 A double can store numbers in this range: -1.79769313486231E308 to -4.94065645841247E-324 for negative values and from 4.94065645841247E-324 to 1.79769313486232E308 for positive values. But only approximately 15 significant digits. -- Regards, Tom Ogilvy "Marston" wrote in message m... Hi I'm trying to prep some values for a multi-column sort in an array. The array contains something like 300,000 by 10 values I've seen quick sort routines that can sort on a single value, but not perform sorts within the initial sort. My idea was to concatenate all my values into a single value and then sort on it. The problem I'm facing is that my numbers would have to be on the order of ~10E+18-1 long The way the routine works is it looks at the numbers in each column whose length is fixed and never has a situation where there are leading zeros to cause problems. Col 1 Col 2 Col 3 Col 4 Col 5 Col 6 Row x: aaa bbb cc ddddd e fff I only need to arrange based on these 6 columns not the entire 10 in the array My problem is when I create a number like aaabbbccdddddefff it appears to have problems with the "efff" portion. I checked each individual component (which I'm multiplying by some 1nnnn where n is some number of zeros - and they all work fine, its just once I tried to add things. Its like it drops values off. I have the variables defined as Double which I thought would work. Any thoughts? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
question about the data type in excel | Excel Discussion (Misc queries) | |||
Data Sort Question | Excel Discussion (Misc queries) | |||
Data Sort 2 part question | Excel Discussion (Misc queries) | |||
a sort of GROUP BY data extraction question... | Excel Worksheet Functions | |||
Newbie Question: Data/Sort | New Users to Excel |