Automatically Create SKU's from Product Phrases

luxehouseofcouture

New Member
Joined
Dec 26, 2018
Messages
11
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.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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
ABCDEFGHIJKO
1HandleTitleBody (HTML)VendorTypeTagsPublishedOption1 NameOption1 ValueOption2 NameOption2 ValueSKU Combo
2shirley-brushed-waffle-tunicShirley Brushed Waffle TunicZutterTunicsCambodiaTRUEColorBLACKSizeSMALLZUTT-TUNI-SBWT-B-S
3transition-cardiganTransition CardiganLove TreeCardigansChinaTRUEColorYELLOWSizeLARGELOTR-CARD-TC-Y-L
4skinny-jeansSkinny Jeans1 Day at a TimeJeansChinaTRUEColorLight RUST/BLACKSizeSMALL/MEDIUM0-JEAN-SJ-LRB-SM
Example
Cell Formulas
RangeFormula
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..
 
Last edited:
Upvote 0
@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...
 
Upvote 0
@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!

Code:
=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)))
 
Upvote 0
@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.
 
Upvote 0
@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).
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top