Just a bit more on this least-significant first question ...
I think there is confusion here, because of the fact you do one or the other
at times.
Specifically, if you are doing ONE SORT using multiple keys, then you check
from most-significant through to least-significant. (I.e. if the
most-significant keys are different, then you order by those and never check
the lesser keys; if the most-significant keys are the same, then you move
down to the next-most-significant and order by those and so on.)
If you are doing the multi-key sort using SEPARATE SORT PASSES (e.g. for
more than 3 sort keys in Excel 2003), then you sort by the less-significant
keys first. This is so that you can order those records that have equal
more-significant keys, while still allowing the primary keys to be the
dominant ones. A couple of extra points on this: this type of sort only
works when the sort algorithm keeps records with equal sort keys in the
order in which it originally found them (and not all sort algorithms do
this); and the keys within a sort pass are still specified from
more-significant to less, it's just the separate passes that need to work
from less to more.
So, whenever we talk about the order of using sort keys, we need to make it
clear whether we are talking about a single sort pass, or separate sort
passes.
Regards, Dave S
----- Original Message -----
From: "paulschreinerindy" <schreiner_paul@att.net>
To: <ExcelVBA@yahoogroups.com>
Sent: Tuesday, January 10, 2012 5:34 AM
Subject: [ExcelVBA] Re: Sorting in VBA
Lisa,
Sorry I've been delayed in responding to your sorting question.
I see that others have stepped up to provide assistance.
I may be able to help clear up some of the confusion.
There was a question with regard to preference for sorting from Least
Significant to Most Significant.
Let's say you had records with two fields:
Z : 1
A : 3
C : 2
When sorting on the first (most significant) column:
Z is compared to A, and the records are reversed, resulting in:
A: 3
Z : 1
C : 2
Z is compared to C and the records are reversed, resulting in:
A: 3
C : 2
Z : 1
An additional iteration would show that the Most Significant field is now
sorted.
Sorting the Next field:
3 is compared to 2, the records are reversed, resulting in:
C : 2
A : 3
Z : 1
You can immediately see that the sort order has been invalidated.
But, if you were to have sorted the SECOND field (least significant)
Then, by the time you get to the MOST significant criteria, then changes
made to the lesser significant fields would be irrelevant.
Now,
I understand that you have data stored in an array.
The source of the array is irrelevant.
You COULD extract this data and store it in an Excel worksheet, sort it,
then re-load the array.
But you SHOULD be able to process the array without loading another
application.
I have created two separate macro scenarios.
In one scenario, I sort the array elements themselves. (it requires nested
If statements, since you will only sort secondary criteria if the previous
criteria is the same)
I also created a single-dimension array in which I concatenated the array
records IN THE ORDER OF SORT CRITERIA. Then, I reloaded the array.
I was able to get both scenarios to work to get the sort order as described
in your file (I think)
The difference is that: sorting the original array (388 records) took 32
seconds.
Sorting the Concatenated data took only 13 seconds.
This may not be significant NOW, but perhaps if the array gets much larger.
I also think that it may be possible to create a more "flexible" function
with the second option.
That is, it might be able to pass an array to the function that will control
the order in which the array elements are concatenated.
I'd like to send you the file so that you can look at it. if either approach
looks like it might be interesting, you can post it as a solution.
Can I send you the file?
Paul
--- In ExcelVBA@yahoogroups.com, Paul Schreiner <schreiner_paul@...> wrote:
>
> Lisa,
> The current data is over 300 lines...
> In the future, how much data are we talking about?
> Could it conceivable exceed 1000? 10,000?
> Only curious because it may change what approach I'd take.
>
> as for "taking advantage of being a moderator":
> I'm sure the Company Car, the Condo on the Beach (with free maid
> service and
> stocked mini-bar)Â and the use of the company jet for vacations are
> sufficient
> perks for your job as moderator... posting your own attachments would be
> WAY
> over the top! (lol)
>
> Unless the other moderators would be jealous that you're actually getting
> some
> WORK done!
> Then, I suppose the slight delay in rendering assistance can be
> tolerated... ;)
> Â
> Paul
> -----------------------------------------
> â?oDo all the good you can,
> By all the means you can,
> In all the ways you can,
> In all the places you can,
> At all the times you can,
> To all the people you can,
> As long as ever you can.â? - John Wesley
> -----------------------------------------
>
>
>
>
> ________________________________
> From: "1z@..." <1z@...>
> To: ExcelVBA@yahoogroups.com
> Sent: Fri, January 6, 2012 12:44:52 PM
> Subject: Re: [ExcelVBA] Re: Sorting in VBA
>
> Â
>
>
> Paul,
>
> Sure... Makes a *lot* of sense.
> "Fresh" is Good.
> I've "started anew" a number of times now and am still not sure about the
> sort
> creating the the same as Excel.
>
> I'm going to ask for a workbook to be posted. not going to do it myself
> because
> that may be seen as taking advanage of being a moderator... :-).
>
> The data is over 300 lines.. It's a dump of the array I want to sort on
> "columns" / elements 1 then 0 then 6 then 4.
>
> As extra information those are.. Project, Called Procedure, Calling
> Module,Calling Procedure respectively... by which you may have guessed
> it's to
> do with the VBE. I'm trying to populate a treeview with Called procedures,
> number of times called and where they are called from.
>
> The sheet SORT has all of the data in ... Raw format, Excel Sorted format,
> Space
> for Proc sorted format - which is currently a copy of the excel sorted
> data, and
> lastly a space for results which is just =If(Exec Sorted<>Proc
> Sorted,"No","")
>
> I've included a proc to dump the arrays to a sheet and the "ordinary" sort
> proc
> I use.
>
> And Thanks
> Lisa
>
>
>
> Subject: [ExcelVBA] Re: Sorting in VBA
>
> Lisa,
> an you post some sample data? just a few records.
> oth Unsorted and Sorted?
> I think I'd be better off trying to look at it "fresh" rather
> han trying to work through code that ISN'T working and try to
> igure out what it was INTENDED to do that it's not...
> does that make sense?
> Paul S
>
> [Non-text portions of this message have been removed]
>
>
>
>
> [Non-text portions of this message have been removed]
>
------------------------------------
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
here: http://www.mousetrax.com/techtrax to enter the ezine, then search the
ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------Yahoo! Groups Links
-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 10.0.1416 / Virus Database: 2109/4131 - Release Date: 01/08/12
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar