Automatically Create SKU's from Product Phrases

[luxehouseofcouture]

I am in the process of creating a SKU generator for my Shopify products. I want the logic for the SKU's to follow the below template.


Example Product 1:
Vendor: Zutter
Name: Shirley Brushed Waffle Tunic
Color: Light Blue/Red/White
Size: X Large


Example SKU 1:
ZUTT-SBWT-LBRW-XL


Example Product 2:
Vendor: Love Tree
Name: Transition Cardigan
Color: Light Blue
Size: Small


Example SKU 2:
LOTR-TC-LB-S


Things to Note:

• Ideally, I would like to have the formulas reference the Codes tab and use the respective ID value for the adjacent matching value within the generated SKU's (i.e. the code for Booties is Btie).
• Product names variables should be generated as acronyms (i.e. Shirley Brushed Waffle Tunic would output as SSBW)
• Characters for each product variable should max at 4, using the first words in the text string to generate the variable acronym (i.e. Shirley Striped & Brushed Waffle Tunic would output as SSBW)
• In the event of a duplicate SKU, a numerical identifier should be appended to the duplicate (i.e. LT-TC-LB-S and LT-TC-LB-S-1)
• I have too many colors in my shop to create ID codes. So, we'll just need to generate an acronym to accomplish this. if a color is a phrase (i.e. Light Blue), the acronym would be LB. But if it is a multi-color option, the acronym should encompass all color options (i.e. Light Blue/Red/Green would be LBRG). I'd imagine that the formula needs to have an OR condition that looks for " " or "/" as a possible separator in order to accomplish this.
• Variables can be alpha-numerical and must be separated by hyphen's (-).

You can download the example excel sheet here.

 

[dave2018]

pretty complicated...

question: are all the vendors listed in the second sheet? if not, how would you name "1 Day at a Time"?

Drag cell O2 down using the fill handle.

Excel 2016 (Windows) 32 bit
	A	B	C	D	E	F	G	H	I	J	K	O
1	Handle	Title	Body (HTML)	Vendor	Type	Tags	Published	Option1 Name	Option1 Value	Option2 Name	Option2 Value	SKU Combo
2	shirley-brushed-waffle-tunic	Shirley Brushed Waffle Tunic		Zutter	Tunics	Cambodia	TRUE	Color	BLACK	Size	SMALL	ZUTT-TUNI-SBWT-B-S
3	transition-cardigan	Transition Cardigan		Love Tree	Cardigans	China	TRUE	Color	YELLOW	Size	LARGE	LOTR-CARD-TC-Y-L
4	skinny-jeans	Skinny Jeans		1 Day at a Time	Jeans	China	TRUE	Color	Light RUST/BLACK	Size	SMALL/MEDIUM	0-JEAN-SJ-LRB-SM
Example

Cell Formulas
Range	Formula
O2	{=CONCATENATE(IFERROR(INDEX(Codes!$C:$C,MATCH(D2,Codes!$A:$A,0)),0),"-",INDEX(Codes!$G:$G,MATCH(E2,Codes!$E:$E,0)),"-",LEFT(SUBSTITUTE(CONCAT(LEFT(B2,1),MID(B2,SMALL(IF(MID(B2,ROW(INDIRECT("A1:A"&LEN(B2))),1)=" ",ROW(INDIRECT("A1:A"&LEN(B2)))+1),ROW(INDIRECT("A1:A"&LEN(B2)-LEN(SUBSTITUTE(B2," ",""))))),1)),"&",""),4),"-",IF(ISERROR(FIND("/",I2)),LEFT(I2,1),CONCAT(IF(NOT(ISERROR(FIND(" ",I2))),LEFT(I2,1)),MID(I2,FIND(" ",I2)+1,1),MID(I2,SMALL(IF(MID(I2,ROW(INDIRECT("A1:A"&LEN(I2))),1)="/",ROW(INDIRECT("A1:A"&LEN(I2)))+1),ROW(INDIRECT("A1:A"&LEN(I2)-LEN(SUBSTITUTE(I2,"/",""))))),1))),"-",INDEX(Codes!$M:$M,MATCH(K2,Codes!$L:$L,0)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

NOTE: this problem is not fully solved, it will not catch duplicates yet. but try it for now..

 

[RoryA]

Welcome to the forum.

Please take a minute to read the forum rules, especially as regards cross-posting and comply with them in future. Thanks.

Cross-posted here: https://www.excelforum.com/excel-fo...act-first-three-characters-from-one-word.html

 

[luxehouseofcouture]

My apologies, @RoryA. I'll be careful to not do this in the future without abiding by the rules. I don't see how I can edit my post, so for clarification, I have cross-posted at https://www.excelforum.com/excel-fo...act-first-three-characters-from-one-word.html because I know this is a complicated request, and I wanted to increase my chances of receiving help.

 

[luxehouseofcouture]

@dave2018, thank you so much for taking the time to take on this challenging request. To answer your initial question: I provided a sample set of data in my attachment. Per my notes above, "Characters for each product variable should max at 4, using the first words in the text string to generate the variable acronym (i.e. Shirley Striped & Brushed Waffle Tunic would output as SSBW)" - so "1 Day at a Time" would use the code "1DAA" to keep the formula as easy and consistent as possible, while ensuring the least amount of duplicates possible.

Also, I've tested your code and it works almost perfectly. Would you be able to help me make the following adjustments?

* If no vendor exists, use a 1 instead of a 0 (as using a 0 in the front of SKU's will not work properly for all systems)
* Remove the code for Category Types. The SKU is pretty long, and I think the formula will mostly provide unique values without the use of the category code. So, the SKU template should be VENDOR CODE-PRODUCT CODE-COLOR CODE-SIZE CODE. i.e. LOTR-TC-Y-L would be the SKU for the Transition Cardigan product.
* I dragged the formula down and it gave me a 0 for the first product, and a #VALUE ! for the 3rd product. I realized I didn't have the correct Vendor Name for the 1st product, Shirley Brush Waffle Tunic, so I corrected that and it worked. But I cannot figure out why the 3rd product, Skinny Jeans, is not populating a SKU...

 

[luxehouseofcouture]

@dave2018 - I was able to figure out how to resolve the 2nd issue above (removing the category type) using the below code. So if you can provide insights on the other two, that would be really helpful and appreciated - thanks!

=CONCATENATE(IFERROR(INDEX(Codes!$C:$C,MATCH(D2,Codes!$A:$A,0)),0),"-",LEFT(SUBSTITUTE(CONCAT(LEFT(B2,1),MID(B2,SMALL(IF(MID(B2,ROW(INDIRECT("A1:A"&LEN(B2))),1)=" ",ROW(INDIRECT("A1:A"&LEN(B2)))+1),ROW(INDIRECT("A1:A"&LEN(B2)-LEN(SUBSTITUTE(B2," ",""))))),1)),"&",""),4),"-",IF(ISERROR(FIND("/",I2)),LEFT(I2,1),CONCAT(IF(NOT(ISERROR(FIND(" ",I2))),LEFT(I2,1)),MID(I2,FIND(" ",I2)+1,1),MID(I2,SMALL(IF(MID(I2,ROW(INDIRECT("A1:A"&LEN(I2))),1)="/",ROW(INDIRECT("A1:A"&LEN(I2)))+1),ROW(INDIRECT("A1:A"&LEN(I2)-LEN(SUBSTITUTE(I2,"/",""))))),1))),"-",INDEX(Codes!$M:$M,MATCH(K2,Codes!$L:$L,0)))

 

[luxehouseofcouture]

@dave2018 - After further review, I found that issue #3 above is being caused by the value of cell I4 (RUST/BLACK). If I change that with just one color, excluding the /, it populates the SKU properly. Hopefully that info will help guide the solution.

 

[luxehouseofcouture]

@dave2018 - After further review, I found that issue #3 above is being caused by the value of cell I4 (RUST/BLACK). If I change that with just one color, excluding the /, it populates the SKU properly. Hopefully that info will help guide the solution.

Also, when inputting Light Blue, it populates the SKU with L instead of LB. In instances where a phrase is used instead of one word, it should take that into account (i.e. Light Blue should populate as LB, whereas Light Blue/Red/White should populate as LBRW).

 

[dave2018]

ok. I dont have a computer now, but dont worry, i didnt forget about you .

I will adress all your comments when i get a chance. Thank you for the helpful and detailed feedback. It really helps, believe me.

All the best

 

[luxehouseofcouture]

Not a problem @dave2018 - I really appreciate your help on this. One other issue I identified when using the formula is that having single words in the color field is likely to cause duplicates (i.e. copper, coffee, and charcoal would all populate with C). So to correct this, can you have the formula use a minimum of 3 characters for single words in color (i.e. COP, COF, and CHA)? All of the other notes I provided above should still apply.