A strict ASCII sort is rarely a "good thing". For instance, things in lower
case are sorted away from things in upper case; numbers are not treated as
numbers, etc. Some programs (including Excel) try for a more natural sort.
This is superior unless you want to do other comparisons on the sorted data
(such as the binary search here).
Microsoft talks about the sort order in
http://support.microsoft.com/kb/322067 and there are many other Google hits
which might be of interest too. The sort order is also given in Excel help.
Unfortunately, there doesn't seem to be any way to tell Excel to sort in
strict ASCII sequence, so the binary search probably comes down to one of
three scenarios:
- Sort externally. This is quite likely to be achievable. Simply sort your
list before bringing it into Excel and don't sort it again within Excel.
- Sort by VBA. In the same way that the binary search is easy to implement
in VBA, so would a sort function be. Just make sure to implement a
reasonably efficient sort algorithm, not a bubble sort, or you'll be there
all day.
- Let Excel do the searching. I suppose the question arises over why you'd
do a binary search in Excel anyway. Excel's inbuilt lookup facilities are
probably going to be quicker at finding a value than anything written in
VBA.
Regards, Dave S
----- Original Message -----
From: "mickey11030" <mickey11030@yahoo.com>
To: <ExcelVBA@yahoogroups.com>
Sent: Monday, October 10, 2011 3:29 AM
Subject: [ExcelVBA] Re: Binary Search Problem
>I made a effort to present my sort results in carefully aligned columns,
>but I see in the email I received that the columns were destroyed. My
>point, which might still be visible, is that sort order and ASCII value
>order do not agree, and I don't know why. If Excel is not using ASCII
>values to sort, what is it using?
>
> --- In ExcelVBA@yahoogroups.com, "mickey11030" <mickey11030@...> wrote:
>>
>> This is somewhat off topic, but since we're talking about sorting, I
>> thought I'd bring up unexpected behavior I noticed some time ago. I
>> sorted special characters on the Character column and got these results:
>> Name Character ASCII apostrophe '
>> 39 dash - 45 space
>> 32 exclamation ! 33 quote "
>> 34 pound # 35 dollar
>> $ 36 percent % 37
>> ampersand & 38 open paren (
>> 40 close paren ) 41 asterisk *
>> 42 comma , 44 period
>> . 46 slash /
>> 47 plus + 43
>> I assumed Excel sorted according to ASCII values, but you'll notice that
>> the apostrophe, ASCII value 39 and dash, ASCII value 45 sort before the
>> space, ASCII value 32, and that plus, ASCII value 43, sorts after slash,
>> ASCII value 47. Does anyone know what's going on?
>> --- In ExcelVBA@yahoogroups.com, PY & Associates <pynasocas@> wrote:
>> >
>> > Enlightening.
>> >
>> > I modified two lines thus
>> > If not (Dict_SD.exists(UCase(Sheet1.Cells(x, 1).Value))) Then
>> > Sheet1.Cells(x, 4).Value = "Y"
>> > If not (Dict_PH.exists(ucase(Sheet1.Cells(x, 1).Value))) Then
>> > Sheet1.Cells(x, 5).Value = "Y"
>> >
>> > Regards
>> > KC@103
>> > pynasocas
>> >
>> > From: Paul Schreiner schreiner_paul@
>> > To: ExcelVBA@yahoogroups.com
>> > Sent: Friday, 7 October 2011 8:56 PM
>> > Subject: Re: [ExcelVBA] Binary Search Problem
>> >
>> >
>> >
>> > #2.. You're using an Interval Halving technique for searching the
>> list.
>> > However, for it to work properly, the data has to be sorted correctly.
>> >
>> > Unfortunately, it doesn't look like it's sorting properly.
>> > Specifically,
>> > the ASCII code for "-" is 45
>> > the ASCII code for "0" is 48
>> > (1-9 are codes 49-57)
>> >
>> > But in the sort, "-" is sorted AFTER numbers.
>> > (see rows:
>> > 6115: 020-SUB-0011
>> > 6116: 02-16917-3600
>> >
>> > 12513: 400-TUNECVR-0003
>> > 12514: 40-26314
>> > )
>> >
>> > for that reason, when the interval happens to test lines comparing 020
>> to 02-,
>> > it assumes that the 020 should be AFTER 02-, but the next iteration
>> finds that
>> > it is outside the range,
>> > and therefore assumes it cannot find it.
>> > I'm not sure what can be done about this sorting problem, however, I
>> have an
>> > alternate solution.
>> >
>> > Paul
>> >
>> >
>> > [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.1410 / Virus Database: 1520/3943 - Release Date: 10/07/11
>
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