"-" ignored in sort

[Mike H]

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

[Aladin Akyurek]

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?

[Mike H]

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?

[Aladin Akyurek]

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?

[Mike H]

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?

[Dave Peterson]

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

[Myrna Larson]

>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
<[email protected]> wrote:

[Mike H]

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

[Mike H]

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
> <[email protected]> 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