We help IT Professionals succeed at work.

Assign an identifier based off the last character

tols12
tols12 asked
on
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... How to create an identifier?
Comment
Watch Question

AlanConsultant
CERTIFIED EXPERT
Commented:
Hi,

If the SKU is in A1, then something like this?

=IF(RIGHT(A1,1)="5",2,IF(RIGHT(A1,2)="AA",1))
and so on.

If you have more than seven options, it won't work, but for three it's okay.

Alan.
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010

Commented:
Hello,

"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","R"},0)

cheers, teylyn

CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010
Commented:
Or, for a more robust version, checking for "AA"

=IF(RIGHT(A7,2)="AA",1,IFERROR(MATCH(RIGHT(A7,1),{"","5","R"},0),""))

this can be extended to many more than seven different cases within the Match function.

cheers, teylyn
AlanConsultant
CERTIFIED EXPERT

Commented:
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.
Excel VBA Developer
CERTIFIED EXPERT
Top Expert 2014
Commented:
I like the idea of a lookup table (an Excel table, at that, so it can be dynamic).

If the number of testing characters is only one or two, one could insert a nested IFERROR/VLOOKUP statement that should correctly provide the identifier.

Assuming that the lookup table has the testing characters in the first column and the related Identifier in the second column, the formula to derive it would be (in cell B2):
=IFERROR(VLOOKUP(RIGHT(A2,2),Table1,2,FALSE),IFERROR(VLOOKUP(RIGHT(A2,1),Table1,2,FALSE),""))
where "Table1" is the range name of the lookup table.

I've attached an example.

-Glenn Identifier-Lookup.xlsx
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010

Commented:
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
Glenn RayExcel VBA Developer
CERTIFIED EXPERT
Top Expert 2014

Commented:
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
AlanConsultant
CERTIFIED EXPERT

Commented:
Nice Glenn - That's more what I was thinking, and far preferable from a maintenance perspective than Teylyn's approach.

Good work!

Alan.

Author

Commented:
Thank you!!!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.