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
----------------------------------
Tidak ada komentar:
Posting Komentar