+ Reply to Thread
Results 1 to 9 of 9

"-" ignored in sort

  1. #1
    Mike H
    Guest

    "-" ignored in sort

    The hypen character "-" seems to be ignored in a sort that I'm using.

    Here are the details:
    I have a worksheet that uses vendor name and partnumber in the form of
    "vendor-part" that then uses vlookup to query a vendor file with these
    fields applicable fields:

    column a column b column c
    =B1&"-"&C1 vendor part

    Strangely, to me, a sort using column A doesn't produce the same results
    as a sort using column B plus column C.

    For example, using only column A for the sort:
    A-C75A A C75A
    ACB-15 ACB 15
    A-CG1850 A CG1850

    using column A + column B
    A-C75A A C75A
    A-CG1850 A CG1850
    ACB-15 ACB 15

    As a result, if I use TRUE in the range_lookup portion of the vlookup
    function I have no idea what the outcome will be. I don't know if
    vlookup is considering a sorted array in the same way that SORT is.

    I suppose this is a two-part question:
    1) what would the result be of a lookup for A-C8? I seem to get A-C75A
    using a column A sort and #NA using a column B+C sort.
    2) given the sorting behavior, how do I want to sort this table, or
    which character besides a hypen can use to get both good vlookup results
    and sensible visual results as well?
    --
    Mike H

  2. #2
    Aladin Akyurek
    Guest

    Re: "-" ignored in sort

    I guess you need to set the match-type to 0 (or FALSE)...

    =VLOOKUP(LookupValue,Table,ColIdx,0)


    Mike H wrote:
    > The hypen character "-" seems to be ignored in a sort that I'm using.
    >
    > Here are the details:
    > I have a worksheet that uses vendor name and partnumber in the form of
    > "vendor-part" that then uses vlookup to query a vendor file with these
    > fields applicable fields:
    >
    > column a column b column c
    > =B1&"-"&C1 vendor part
    >
    > Strangely, to me, a sort using column A doesn't produce the same results
    > as a sort using column B plus column C.
    >
    > For example, using only column A for the sort:
    > A-C75A A C75A
    > ACB-15 ACB 15
    > A-CG1850 A CG1850
    >
    > using column A + column B
    > A-C75A A C75A
    > A-CG1850 A CG1850
    > ACB-15 ACB 15
    >
    > As a result, if I use TRUE in the range_lookup portion of the vlookup
    > function I have no idea what the outcome will be. I don't know if
    > vlookup is considering a sorted array in the same way that SORT is.
    >
    > I suppose this is a two-part question:
    > 1) what would the result be of a lookup for A-C8? I seem to get A-C75A
    > using a column A sort and #NA using a column B+C sort.
    > 2) given the sorting behavior, how do I want to sort this table, or
    > which character besides a hypen can use to get both good vlookup results
    > and sensible visual results as well?


  3. #3
    Mike H
    Guest

    Re: "-" ignored in sort

    Hi Aladin. Yes, that works, and it's what I've had to resort to in the
    interim. Unfortunately, the next lower part number would be a valid
    response, so I'm stumbling along with a partially broken spreadsheet.

    --
    Mike H

    On Sat, 01 Jan 2005 22:30:06 +0100, Aladin Akyurek wrote:

    > I guess you need to set the match-type to 0 (or FALSE)...
    >
    > =VLOOKUP(LookupValue,Table,ColIdx,0)
    >
    >
    > Mike H wrote:
    >> The hypen character "-" seems to be ignored in a sort that I'm using.
    >>
    >> Here are the details:
    >> I have a worksheet that uses vendor name and partnumber in the form of
    >> "vendor-part" that then uses vlookup to query a vendor file with these
    >> fields applicable fields:
    >>
    >> column a column b column c
    >> =B1&"-"&C1 vendor part
    >>
    >> Strangely, to me, a sort using column A doesn't produce the same results
    >> as a sort using column B plus column C.
    >>
    >> For example, using only column A for the sort:
    >> A-C75A A C75A
    >> ACB-15 ACB 15
    >> A-CG1850 A CG1850
    >>
    >> using column A + column B
    >> A-C75A A C75A
    >> A-CG1850 A CG1850
    >> ACB-15 ACB 15
    >>
    >> As a result, if I use TRUE in the range_lookup portion of the vlookup
    >> function I have no idea what the outcome will be. I don't know if
    >> vlookup is considering a sorted array in the same way that SORT is.
    >>
    >> I suppose this is a two-part question:
    >> 1) what would the result be of a lookup for A-C8? I seem to get A-C75A
    >> using a column A sort and #NA using a column B+C sort.
    >> 2) given the sorting behavior, how do I want to sort this table, or
    >> which character besides a hypen can use to get both good vlookup results
    >> and sensible visual results as well?


  4. #4
    Aladin Akyurek
    Guest

    Re: "-" ignored in sort

    You seem to have an area in A:C as lookup table. It seems that column A
    is a concatenation of column B and C. You should be to do lookup with
    match-type set to 0 if A:C is not sorted on column A...

    =VLOOKUP(X2,A:C,3,0)

    where X2 houses a lookup value. The value in X2 must have the same
    structure as the values in column A.

    If you sort A:C on A in ascending order, you should be able to do a
    faster lookup...

    =IF(LOOKUP(X2,A:A)=X2,LOOKUP(X2,A:A,C:C),"")

    Mike H wrote:
    > Hi Aladin. Yes, that works, and it's what I've had to resort to in the
    > interim. Unfortunately, the next lower part number would be a valid
    > response, so I'm stumbling along with a partially broken spreadsheet.
    >
    > --
    > Mike H
    >
    > On Sat, 01 Jan 2005 22:30:06 +0100, Aladin Akyurek wrote:
    >
    >
    >>I guess you need to set the match-type to 0 (or FALSE)...
    >>
    >>=VLOOKUP(LookupValue,Table,ColIdx,0)
    >>
    >>
    >>Mike H wrote:
    >>
    >>>The hypen character "-" seems to be ignored in a sort that I'm using.
    >>>
    >>>Here are the details:
    >>>I have a worksheet that uses vendor name and partnumber in the form of
    >>>"vendor-part" that then uses vlookup to query a vendor file with these
    >>>fields applicable fields:
    >>>
    >>>column a column b column c
    >>>=B1&"-"&C1 vendor part
    >>>
    >>>Strangely, to me, a sort using column A doesn't produce the same results
    >>>as a sort using column B plus column C.
    >>>
    >>>For example, using only column A for the sort:
    >>>A-C75A A C75A
    >>>ACB-15 ACB 15
    >>>A-CG1850 A CG1850
    >>>
    >>>using column A + column B
    >>>A-C75A A C75A
    >>>A-CG1850 A CG1850
    >>>ACB-15 ACB 15
    >>>
    >>>As a result, if I use TRUE in the range_lookup portion of the vlookup
    >>>function I have no idea what the outcome will be. I don't know if
    >>>vlookup is considering a sorted array in the same way that SORT is.
    >>>
    >>>I suppose this is a two-part question:
    >>>1) what would the result be of a lookup for A-C8? I seem to get A-C75A
    >>>using a column A sort and #NA using a column B+C sort.
    >>>2) given the sorting behavior, how do I want to sort this table, or
    >>>which character besides a hypen can use to get both good vlookup results
    >>>and sensible visual results as well?


  5. #5
    Mike H
    Guest

    Re: "-" ignored in sort

    Aladin, you're right in your first statements below; that's exactly what
    I'm doing now (except I can't use it as is for next-lower value
    lookups). Still, it is a large spreadsheet with 5 other linked
    spreadsheets and the vlookup is slow.

    I'm going to try out your suggestion for vector lookups (using the "if
    found" test) and see what happens. It'll be a while, but I'll post back
    any observations. It should be interesting. Thanks for the tip.

    Mike

    On Sat, 01 Jan 2005 23:43:30 +0100, Aladin Akyurek wrote:

    > You seem to have an area in A:C as lookup table. It seems that column A
    > is a concatenation of column B and C. You should be to do lookup with
    > match-type set to 0 if A:C is not sorted on column A...
    >
    > =VLOOKUP(X2,A:C,3,0)
    >
    > where X2 houses a lookup value. The value in X2 must have the same
    > structure as the values in column A.
    >
    > If you sort A:C on A in ascending order, you should be able to do a
    > faster lookup...
    >
    > =IF(LOOKUP(X2,A:A)=X2,LOOKUP(X2,A:A,C:C),"")
    >
    > Mike H wrote:
    >> Hi Aladin. Yes, that works, and it's what I've had to resort to in the
    >> interim. Unfortunately, the next lower part number would be a valid
    >> response, so I'm stumbling along with a partially broken spreadsheet.
    >>
    >> --
    >> Mike H
    >>
    >> On Sat, 01 Jan 2005 22:30:06 +0100, Aladin Akyurek wrote:
    >>
    >>
    >>>I guess you need to set the match-type to 0 (or FALSE)...
    >>>
    >>>=VLOOKUP(LookupValue,Table,ColIdx,0)
    >>>
    >>>
    >>>Mike H wrote:
    >>>
    >>>>The hypen character "-" seems to be ignored in a sort that I'm using.
    >>>>
    >>>>Here are the details:
    >>>>I have a worksheet that uses vendor name and partnumber in the form of
    >>>>"vendor-part" that then uses vlookup to query a vendor file with these
    >>>>fields applicable fields:
    >>>>
    >>>>column a column b column c
    >>>>=B1&"-"&C1 vendor part
    >>>>
    >>>>Strangely, to me, a sort using column A doesn't produce the same results
    >>>>as a sort using column B plus column C.
    >>>>
    >>>>For example, using only column A for the sort:
    >>>>A-C75A A C75A
    >>>>ACB-15 ACB 15
    >>>>A-CG1850 A CG1850
    >>>>
    >>>>using column A + column B
    >>>>A-C75A A C75A
    >>>>A-CG1850 A CG1850
    >>>>ACB-15 ACB 15
    >>>>
    >>>>As a result, if I use TRUE in the range_lookup portion of the vlookup
    >>>>function I have no idea what the outcome will be. I don't know if
    >>>>vlookup is considering a sorted array in the same way that SORT is.
    >>>>
    >>>>I suppose this is a two-part question:
    >>>>1) what would the result be of a lookup for A-C8? I seem to get A-C75A
    >>>>using a column A sort and #NA using a column B+C sort.
    >>>>2) given the sorting behavior, how do I want to sort this table, or
    >>>>which character besides a hypen can use to get both good vlookup results
    >>>>and sensible visual results as well?


  6. #6
    Dave Peterson
    Guest

    Re: "-" ignored in sort

    From xl2002's help (for: Troubleshoot sorting)

    Apostrophes (') and hyphens (-) are ignored, with one exception: If two text
    strings are the same except for a hyphen, the text with the hyphen is sorted
    last.

    Can you use a period instead?

    or even
    =b1&char(10)&c1
    This will be like an alt-enter if the cell is set for wrap text--else it'll show
    a little square.


    Mike H wrote:
    >
    > The hypen character "-" seems to be ignored in a sort that I'm using.
    >
    > Here are the details:
    > I have a worksheet that uses vendor name and partnumber in the form of
    > "vendor-part" that then uses vlookup to query a vendor file with these
    > fields applicable fields:
    >
    > column a column b column c
    > =B1&"-"&C1 vendor part
    >
    > Strangely, to me, a sort using column A doesn't produce the same results
    > as a sort using column B plus column C.
    >
    > For example, using only column A for the sort:
    > A-C75A A C75A
    > ACB-15 ACB 15
    > A-CG1850 A CG1850
    >
    > using column A + column B
    > A-C75A A C75A
    > A-CG1850 A CG1850
    > ACB-15 ACB 15
    >
    > As a result, if I use TRUE in the range_lookup portion of the vlookup
    > function I have no idea what the outcome will be. I don't know if
    > vlookup is considering a sorted array in the same way that SORT is.
    >
    > I suppose this is a two-part question:
    > 1) what would the result be of a lookup for A-C8? I seem to get A-C75A
    > using a column A sort and #NA using a column B+C sort.
    > 2) given the sorting behavior, how do I want to sort this table, or
    > which character besides a hypen can use to get both good vlookup results
    > and sensible visual results as well?
    > --
    > Mike H


    --

    Dave Peterson

  7. #7
    Myrna Larson
    Guest

    Re: "-" ignored in sort

    >The hypen character "-" seems to be ignored in a sort that I'm using.

    Yes, that's what you'll find in Help.

    On Sat, 1 Jan 2005 13:20:37 -0800, Mike H
    <mkREMOVEhuskeyALL@THIShotmail.invalid> wrote:




  8. #8
    Mike H
    Guest

    Re: "-" ignored in sort

    That's it! Used an underscore instead and we're off and running. Thanks,
    Dave. btw, I can think of uses for the char(10) tip as well

    On Sat, 01 Jan 2005 18:35:39 -0600, Dave Peterson wrote:

    > From xl2002's help (for: Troubleshoot sorting)
    >
    > Apostrophes (') and hyphens (-) are ignored, with one exception: If two text
    > strings are the same except for a hyphen, the text with the hyphen is sorted
    > last.
    >
    > Can you use a period instead?
    >
    > or even
    > =b1&char(10)&c1
    > This will be like an alt-enter if the cell is set for wrap text--else it'll show
    > a little square.
    >
    >
    > Mike H wrote:
    >>
    >> The hypen character "-" seems to be ignored in a sort that I'm using.
    >>
    >> Here are the details:
    >> I have a worksheet that uses vendor name and partnumber in the form of
    >> "vendor-part" that then uses vlookup to query a vendor file with these
    >> fields applicable fields:
    >>
    >> column a column b column c
    >> =B1&"-"&C1 vendor part
    >>
    >> Strangely, to me, a sort using column A doesn't produce the same results
    >> as a sort using column B plus column C.
    >>
    >> For example, using only column A for the sort:
    >> A-C75A A C75A
    >> ACB-15 ACB 15
    >> A-CG1850 A CG1850
    >>
    >> using column A + column B
    >> A-C75A A C75A
    >> A-CG1850 A CG1850
    >> ACB-15 ACB 15
    >>
    >> As a result, if I use TRUE in the range_lookup portion of the vlookup
    >> function I have no idea what the outcome will be. I don't know if
    >> vlookup is considering a sorted array in the same way that SORT is.
    >>
    >> I suppose this is a two-part question:
    >> 1) what would the result be of a lookup for A-C8? I seem to get A-C75A
    >> using a column A sort and #NA using a column B+C sort.
    >> 2) given the sorting behavior, how do I want to sort this table, or
    >> which character besides a hypen can use to get both good vlookup results
    >> and sensible visual results as well?
    >> --
    >> Mike H



    --
    Mike H

  9. #9
    Mike H
    Guest

    Re: "-" ignored in sort

    On Sat, 01 Jan 2005 23:46:39 -0600, Myrna Larson wrote:

    >>The hypen character "-" seems to be ignored in a sort that I'm using.

    >
    > Yes, that's what you'll find in Help.
    >
    > On Sat, 1 Jan 2005 13:20:37 -0800, Mike H
    > <mkREMOVEhuskeyALL@THIShotmail.invalid> wrote:


    and so it is, when after reading your post I thought to use the key
    words "sort order". I've been sorting for so many years without the
    issue coming up that it just didn't occur to me to query help on such a
    basic level. Perhaps it hasn't always been that way. In any event, yes,
    you're right.
    --
    Mike H

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1