Generating AlphaNumeric SKU by dropdown list

**kebgx6** · 06-22-2010, 09:52 AM

Hi all, I am having trouble figuring out generate a SKU based on selected conditions. Here is the detailed problem:

Basically I have a 15 character SKU and each character is dependent on a selection of an assembly. For example, The first letter in the SKU is representative of the unit type, so If had a drop down box with 5 choices of unit type, the first letter of the SKU would change based on my selection. Ok, thats the easy part (I could just use IF statements). Now we move to the second selection (which also has 5 different options). So right now lets say after one selection my SKU looks like this:

BXXXXX-XXXXX-XXXXX

Now the first selection can be coupled with another 5 different selections and the amount of options get exponentially greater. So if I chose option 1(B) in the first dropdown box, and option 1(Z) in the second dropdown box, it would look like this:

BZXXX-XXXXX-XXXXX

but now I have other combinations that simply cannot be satisfied due to the limited amount of nested IF/AND statements, and as you can see as I move into more and more options there are going to be hundreds of different combinations. Is there an easy way to go about this?? Any help would be greatly appreciated!

P.S. I'm sorry if there is another topic on this, but I couldn't decipher which one would pertain to my problem specifically...

**JBeaucaire** · 06-22-2010, 10:08 AM

This is easier than you realize. In the final SKU cell you are simply going to concatenate the results of the previous "drop down" choice cells, like so:

=A1 & B1 & C1 & D1 & "-" & E1 & "-" & F1

You get the idea. The "&" is pulling the text strings together into one text string.

**kebgx6** · 06-22-2010, 10:34 AM

I'm not sure if I follow you... I'll be a little more specific in my example:

Lets say these are the option in Dropdown menu 1:

Top
Bottom
Left
Right

Top is represented by the letter T in the SKU, Bottom is B, etc... so it can look like one of 4 ways after the first option selection (I just want it to generate the SKU in 1 cell):

TXXXX-XXXXX-XXXXX or BXXXX-XXXXX-XXXXXX or LXXXX-XXXXX-XXXXX, etc...

now dropdown menu 2 contains:

Front
Side
Diagonal

so the combinations get more complicated... TFXXX-XXXXX-XXXXX or TSXXX-XXXXX-XXXXX or TDXXX-XXXXX-XXXXX or BFXX....etc...

If you could be a little more specific or dumb it down a little for me, I would appreciate it haha..

**JBeaucaire** · 06-22-2010, 12:36 PM

First you need to understand the Concatenation technique shown.

A1 = Cat
B1 = 123

=A1 & B1 results in
Cat123

Now, we can evaluate each cell individually too as we concatenate values. In your example, it sounds like you want the FIRST LETTER of the value chosen in A1, and the same for B1.

=LEFT(A1,1) & LEFT(B1,1) results in
C1

You can continue to build your concatenation formula the same way.