If the data is being left as variable length, it is important to pick a
delimiter that is less (in sort terms) than the characters that are likely
to be in the data. Consider the first and third records:
Abc def
A bcdef
using a delimiter of vertical bar
Abc|def
A|bcdef
Vertical bar is higher than b, so the records will stay in this order, which
is probably what you don't want.
Using a delimiter of tab (which I'll show here as >)
Abc>def
A>bcdef
Tab is less than b, so the records will swap positions.
I tend to use delimiters purely to assist human readability; I do not use
them to help the sort. Instead, I make sure that all fields are
space-filled to their full lengths (text) or leading-zero-filled (numbers)
so that the two combined keys can simply be compared. (I do include
delimiters, but purely so that I can see what is going on.)
Regards, Dave S
----- Original Message -----
From: "David Braithwaite" <dbraithwaite@charter.net>
To: <ExcelVBA@yahoogroups.com>
Sent: Saturday, January 07, 2012 7:28 AM
Subject: [unclassified] Re: [ExcelVBA] Re: Sorting in VBA
> Hi again
>
>
>
> The sort does not "depend" on the delimiter --- merely pick one that isn't
> in the data being sorted (it could be single or multi character)
>
>
>
> The reason is that without a delimiter between fields you will not get
> correct results if data in a field is not all the same length. Consider
>
>
>
> Row Field1 field2 field3 concationation
>
> 1 Abc def b Abcdefb
>
> 2 Abcd ef d Abcdefd
>
> 3 A bcdef z Abcdefz
>
>
>
> Based on the concatenated values, these are already sorted.
>
> But the sort order should be 3,1,2
>
>
>
> Also -(and I forgot to mention this in the prior post) when concatenating
>
> Numeric values, you must have the same number of integer digits for the
> numbers in a specific field (values: 2, 200, 2000 need to become
> 0002,0200,2000)
>
>
>
>
>
> You asked achieving the correct order by sorting once on each key and
> thought it odd that I would say to sort on the least significant field
> first
> progressing to most signicant.
>
> Consider
>
>
>
>
>
> Row 1 = A, A, C
>
> Row 2= A, B, B
>
> Row3= B, B, A
>
> Row4 = A,B,A
>
>
>
> Sorting in order of field (field 1 being prime key, field 2 being 2nd
> key, and field 3 being least significant)
>
> Sort on Field 1
>
>
>
> Row 1 = A, A, C
>
> Row 2= A, B, B
>
> Row3= A, B, A
>
> Row4 = B,B,A
>
>
>
> Then Sort results on Field 2
>
>
>
> Row1 = A,A,C
>
> Row 2= A, B, B
>
> Row3= A, B, A
>
> Row 4 = B, B, A
>
>
>
> Then sort this result on Field 3
>
>
>
> Row2= A, B, A
>
> Row 3 = B, B, A
>
> Row 1= A, B, B
>
> Row4 = A,A,C
>
>
>
> As you can see, the last field is in order, but not the first
>
>
>
> However if you sort on the least significant key first (field 3) first,
> and
> then progress to most-significant ( field 2 then field 1) you will get
> correct result
>
>
>
> Again start with
>
>
>
> Row 1 = A, A, C
>
> Row 2= A, B, B
>
> Row3= B, B, A
>
> Row4 = A,B,A
>
>
>
> Sort 1 on field 3
>
>
>
> Row 1 = B, B, A
>
> Row2= A, B, A
>
> Row 3= A, B, B
>
> Row4= A,A,C
>
> Sort result on field 2
>
>
>
> Row4= A,A,C
>
> Row 1 = B, B, A
>
> Row2= A, B, A
>
> Row 3= A, B, B
>
>
>
> Sort result on field 3
>
>
>
> Row4= A,A,C
>
> Row2= A, B, A
>
> Row 3= A, B, B
>
> Row 1 = B, B, A
>
>
>
> The table is now in order with respect to all 3 fields
>
>
>
> If you are doing your own sorting (not using Excel) then you will need to
> take special precautions for numeric values (if you compare them as
> strings,
> then you again need equal number of integer digits, or you need to compare
> them numerically)
>
>
>
> db
>
>
>
>
>
>
>
> [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/4125 - Release Date: 01/05/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