Very well summarized, Dave.
Regards, Steve.
--- "David Smart" <smartware.consulting@...> wrote:
>
> 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@...>
> 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