Thank you, I never realized.
--- In ExcelVBA@yahoogroups.com, "David Smart" <smartware.consulting@...> wrote:
>
> 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@...>
> 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