tols12
asked on
Assign an identifier based off the last character
I have a list of SKU's. I am trying to find a way where i can assign an identifier based on the last character of that SKU. Some SKU's end in a number, others end in a letter. Ex. any sku that ends in AA, i want to assign an identifier "1". If a sku ends in 5, i want to assign an identifier "2". If a sku ends in R, i want to assign an identifier "3"...etc. Many times i will have multiple sku's that end in AA or 5 or R, etc. Does that make sense? I attached a screenshot... 
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi Teylyn,
I can't see how you can expand that beyond seven though?
If he has, for example:
AA
BB
CC
DD
EE
FF
GG
HH
5
R
X
all that need matching, can you still make it work?
My thinking is that it would be more user-maintainable if there was a lookup table, matching the strings to the identifier. The problem then would be how do you match a variable number of characters from the end of the SKU?
Alan.
I can't see how you can expand that beyond seven though?
If he has, for example:
AA
BB
CC
DD
EE
FF
GG
HH
5
R
X
all that need matching, can you still make it work?
My thinking is that it would be more user-maintainable if there was a lookup table, matching the strings to the identifier. The problem then would be how do you match a variable number of characters from the end of the SKU?
Alan.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Alan, I was referring to the Match function. (" ... more than seven different cases within the Match function.")
With Excel 2007 you can also nest more than 7 IF statements, but it is never a good idea. Too hard to maintain. With more than a three or so nesting levels in any version, a lookup as Glenn suggests should be the preferred approach.
The Match() variant I offered is a shortcut if the values to be returned are in a numerical sequence.
cheers, teylyn
With Excel 2007 you can also nest more than 7 IF statements, but it is never a good idea. Too hard to maintain. With more than a three or so nesting levels in any version, a lookup as Glenn suggests should be the preferred approach.
The Match() variant I offered is a shortcut if the values to be returned are in a numerical sequence.
cheers, teylyn
Whoops...forgot an import step regarding lookups. If looking at numbers and alpha characters, the numbers MUST be entered as text values in the lookup table or they will not return results.
I corrected my spreadsheet example. Sorry for the confusion. :-}
Identifier-Lookup.xlsx
I corrected my spreadsheet example. Sorry for the confusion. :-}
Identifier-Lookup.xlsx
Nice Glenn - That's more what I was thinking, and far preferable from a maintenance perspective than Teylyn's approach.
Good work!
Alan.
Good work!
Alan.
ASKER
Thank you!!!
"AA" are two characters, not one. Will there be SKU's that end with "A" but the last but one character a different one?
If you want to check only the last character, then try
=MATCH(RIGHT(A6,1),{"A","5
cheers, teylyn