#1   Report Post  
Mike H
 
Posts: n/a
Default "-" 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   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

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   Report Post  
Mike H
 
Posts: n/a
Default

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   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

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   Report Post  
Mike H
 
Posts: n/a
Default

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   Report Post  
Dave Peterson
 
Posts: n/a
Default

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   Report Post  
Myrna Larson
 
Posts: n/a
Default

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
wrote:



  #8   Report Post  
Mike H
 
Posts: n/a
Default

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   Report Post  
Mike H
 
Posts: n/a
Default

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
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
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
sort cells with hyphen #####-###-#### bagman Excel Discussion (Misc queries) 6 January 1st 05 08:05 PM
sort cells with hyphen #####-###-#### infurtaris Excel Discussion (Misc queries) 0 January 1st 05 06:15 PM
Data > Sort function amnesia? Dave D Excel Discussion (Misc queries) 1 November 29th 04 11:44 PM
How do I sort a column of formulas in Excel? Gordon Excel Discussion (Misc queries) 2 November 27th 04 02:55 AM
How do I sort a column of formulas in Excel? Gordon Excel Discussion (Misc queries) 0 November 26th 04 04:19 PM


All times are GMT +1. The time now is 03:24 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"