SKU Creation Formula

[Kevin_Harrison]

Hey all,

I’m attempting to create a formula that will automatically create SKUs from several different columns, but I’m running into a few issues.

Below is the formula I have so far:

SWITCH(

{Brand Code},

"Kala","KA",

"UBASS","UB",

"Makala","MK",

"Ukadelic","UK",

"Kala Percussion","KP",

"Kala Elite USA","ELT"

)&"-"&

SWITCH(

{Product Type},

"Acoustic-Electric","AE-",

"Guitar","GTR-"

)

&SWITCH(

{Type Code},

"Solid Top (2)","2",

"All Solid (3)","3",

"Solid Body (6)","6"

)

&SWITCH(

{Top Wood Code},

"Cedar","C",

"Spruce","S",

"Striped Ebony","SE",

"Koa","K",

"Acacia","A",

"Mahogany","M"

)

&SWITCH(

{Back & Sides},

"Mahogany","M",

"Rosewood","R"

)

&SWITCH(

{Size Code},

"Tenor","-T"

)Preformatted text

I want the {Back & Sides} portion of the formula to only show up if it doesn’t match with the {Top Wood Code}. For example in row 3 I have a ukulele that has its top, back, and side wood listed as Mahogany, but my current formula makes the SKU “MK-MM”. I want it to be “MK-M”. My formula knowledge is pretty basic so any help will be greatly appreciated.

Reference photo:

[TheTimeSavingCo]

Hi @Kevin_Harrison, I think we could just use an IF() to check whether the the top matches the back and sides and output the one switch instead of two

I’ve put something together below that should work I think, but as I can’t test it I can’t guarantee anything

SWITCH(
  {Brand Code},
  "Kala","KA",
  "UBASS","UB",
  "Makala","MK",
  "Ukadelic","UK",
  "Kala Percussion","KP",
  "Kala Elite USA","ELT"
) &
"-" &
SWITCH(
  {Product Type},
  "Acoustic-Electric","AE-",
  "Guitar","GTR-"
) &
SWITCH(
  {Type Code},
  "Solid Top (2)","2",
  "All Solid (3)","3",
  "Solid Body (6)","6"
) &
IF(
  {Top Wood Code} = {Back & Sides},
  SWITCH(
    {Top Wood Code},
    "Cedar","C",
    "Spruce","S",
    "Striped Ebony","SE",
    "Koa","K",
    "Acacia","A",
    "Mahogany","M"
  ),
  SWITCH(
    {Top Wood Code},
    "Cedar","C",
    "Spruce","S",
    "Striped Ebony","SE",
    "Koa","K",
    "Acacia","A",
    "Mahogany","M"
  ) & 
  SWITCH(
    {Back & Sides},
    "Mahogany","M",
    "Rosewood","R"
  )
) &
SWITCH(
  {Size Code},
  "Tenor","-T"
)

[Alexey_Gusev · Accepted solution]

Hi,
Just curious.
Why repeat {Top Wood Code} twice? ))
sometimes in such cases people even rewrite whole formula, instead of shortest version - doing exactly task statement. I mean, substitute

SWITCH({Back & Sides},
"Mahogany","M",
"Rosewood","R")

for

IF({Back & Sides}!={Top Wood Code},
SWITCH({Back & Sides},
"Mahogany","M",
"Rosewood","R")
)

...SWITCH(
  {Type Code},
  "Solid Top (2)","2",
  "All Solid (3)","3",
  "Solid Body (6)","6"
) &
  SWITCH(
    {Top Wood Code},
    "Cedar","C",
    "Spruce","S",
    "Striped Ebony","SE",
    "Koa","K",
    "Acacia","A",
    "Mahogany","M"
  ) & 
IF({Back & Sides}!={Top Wood Code},
 SWITCH(
    {Back & Sides},
    "Mahogany","M",
    "Rosewood","R"
  )
) &
SWITCH(
  {Size Code},
  "Tenor","-T"
).....

[TheTimeSavingCo]

Oooh, yes, you’re right, your way is a lot cleaner and wouldn’t require changes to be made in two places

[Kevin_Harrison]

Thanks for the assistance on this guys, I actually got pretty close to this yesterday, I was just missing the “!”. The rest of the formula should be a breeze!