Home 
Search 
Today's Posts 
#1




"" 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 "vendorpart" 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: AC75A A C75A ACB15 ACB 15 ACG1850 A CG1850 using column A + column B AC75A A C75A ACG1850 A CG1850 ACB15 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 twopart question: 1) what would the result be of a lookup for AC8? I seem to get AC75A 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




I guess you need to set the matchtype 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 "vendorpart" 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: AC75A A C75A ACB15 ACB 15 ACG1850 A CG1850 using column A + column B AC75A A C75A ACG1850 A CG1850 ACB15 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 twopart question: 1) what would the result be of a lookup for AC8? I seem to get AC75A 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




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 matchtype 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 "vendorpart" 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: AC75A A C75A ACB15 ACB 15 ACG1850 A CG1850 using column A + column B AC75A A C75A ACG1850 A CG1850 ACB15 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 twopart question: 1) what would the result be of a lookup for AC8? I seem to get AC75A 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




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 matchtype 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 matchtype 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 "vendorpart" 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: AC75A A C75A ACB15 ACB 15 ACG1850 A CG1850 using column A + column B AC75A A C75A ACG1850 A CG1850 ACB15 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 twopart question: 1) what would the result be of a lookup for AC8? I seem to get AC75A 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




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 nextlower 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 matchtype 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 matchtype 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 "vendorpart" 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: AC75A A C75A ACB15 ACB 15 ACG1850 A CG1850 using column A + column B AC75A A C75A ACG1850 A CG1850 ACB15 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 twopart question: 1) what would the result be of a lookup for AC8? I seem to get AC75A 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




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 altenter if the cell is set for wrap textelse 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 "vendorpart" 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: AC75A A C75A ACB15 ACB 15 ACG1850 A CG1850 using column A + column B AC75A A C75A ACG1850 A CG1850 ACB15 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 twopart question: 1) what would the result be of a lookup for AC8? I seem to get AC75A 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




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




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 altenter if the cell is set for wrap textelse 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 "vendorpart" 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: AC75A A C75A ACB15 ACB 15 ACG1850 A CG1850 using column A + column B AC75A A C75A ACG1850 A CG1850 ACB15 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 twopart question: 1) what would the result be of a lookup for AC8? I seem to get AC75A 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




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 
Display Modes  


Similar Threads  
Thread  Forum  
sort cells with hyphen ############  Excel Discussion (Misc queries)  
sort cells with hyphen ############  Excel Discussion (Misc queries)  
Data > Sort function amnesia?  Excel Discussion (Misc queries)  
How do I sort a column of formulas in Excel?  Excel Discussion (Misc queries)  
How do I sort a column of formulas in Excel?  Excel Discussion (Misc queries) 