Create a list of base sku's or product groups

[Plank22]

Please help,

Skus are in column A, Color is column B, All I need is if column B =Satin Bronze remove 10 from the end of the sku of column A

There are about 15 finishes so I will need to edit for each one but thus is the best wat to get to the base sku's.
In the sample row 1 is Satin Bronze and its code is 10
In the sample row 2 is Oil Rubbed Bronze and its code is 10B

and so on...

Can I attached a sample file?

 

[Plank22]

We need a base SKU for all items that are the same as an identifier to group the product together when uploading with a script. We also use this in the title of our products.

What you gave me works but if I have already ran it for another finish and dragged down for the 125k rows it erases the finish already completed.


Example; =IF(B2="Satin Bronze",SUBSTITUTE(A2,"10","",(LEN(A2)-LEN(SUBSTITUTE(A2,"10","")))/LEN("10")),"") Ran 1st

=IF(B3="Oil Rubbed Bronze",SUBSTITUTE(A3,"10B","",(LEN(A3)-LEN(SUBSTITUTE(A3,"10B","")))/LEN("10B")),"") Ran second erases all from above formula that are below row 3.

 

[Eric W]

Of course it will. Formulas are dynamic, if you change the source data, or the formula, the result will change also. It sounds like what you want to do is to put in the formula for the first finish down the entire column, then copy that column, then Paste Special > Values back to column A, then change the formula and repeat for every finish. Or maybe have the formula for finish 1 in column C referring to column A, then the formula for finish 2 in column D referring to column C, and so on.

Both very time-consuming, laborious procedures. I still believe it would be much easier and faster to build the Color/Code table, and then you only have to have ONE formula. For example:

	A	B	C	D	E	F	G
SKU	Color	Base SKU	Color Code	Color	Code
100T312.BX
100T312	10B
100T31215
100T31215	10B
100T31215
100T31215	26D
100T31215
100T31215
100T31215
100T31215	32D

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]100T31210.BX[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Satin Bronze[/TD]

[TD="align: right"]10>[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Satin Bronze[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]100T31210B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Oil Rubbed Bronze[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Oil Rubbed Bronze[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]10B[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]100T3121510[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Satin Bronze[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Bright Chrome[/TD]
[TD="align: right"]26[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]100T3121510B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Oil Rubbed Bronze[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Satin Chrome[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]26D[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]100T3121526[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Bright Chrome[/TD]

[TD="align: right"]26[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Aluminum[/TD]
[TD="align: right"]28[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]100T3121526D[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Satin Chrome[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Bright Brass[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]100T3121528[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Aluminum[/TD]

[TD="align: right"]28[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Bright Stainless Steel[/TD]
[TD="align: right"]32[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]100T312153[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Bright Brass[/TD]

[TD="align: right"]3[/TD]
[TD="align: rig"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Satin Stainless Steel[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]32D[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]100T3121532[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Bright Stainless Steel[/TD]

[TD="align: right"]32[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]100T3121532D[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Satin Stainless Steel[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=IFERROR(SUBSTITUTE(A2,D2,"",(LEN(A2)-LEN(SUBSTITUTE(A2,D2,"")))/LEN(D2)),A2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=VLOOKUP(B2,$F$2:$G$9,2,0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



I built the table in F:G, and I used a helper column in D, but when you put the formulas in C2 and D2 and drag down, you NEVER have to change them again!

 

[Plank22]

Thank you for your help,

Can you tell me how I can build the table. I can copy and paste F but how do I populate G?

 

[Eric W]

For column F, copy column B and paste it to F. Then use the Remove Duplicates tool on the Data tab to get just one of each color. To populate G, I honestly don't know how to do it, since I don't know if the color code is 1, 2, 3 or more digits. Your examples vary.

You could do an INDEX/MATCH formula. In G2, put:

=INDEX(A$2:A$100000,MATCH(F2,B$2:B$100000,0))

and drag down to the bottom of the list in G. This will show the first SKU for each color. Then you can somehow decide how many digits the color code has, and overwrite the formula with that code. That's the best I can suggest.

 

[Plank22]

I did it the way you suggested but some still are not removed. How about making it so whatever is in column e is removed from the sku as long as it is on the end of the sku?

Example of what did not work

[TABLE="width: 814"]
<colgroup><col><col span="2"><col span="2"></colgroup><tbody>[TR]
[TD]110S81628[/TD]
[TD]Aluminum[/TD]
[TD]110S816[/TD]
[TD]Aluminum[/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD]110S8163[/TD]
[TD]Bright Brass[/TD]
[TD]110S816[/TD]
[TD]Bright Brass[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]110S81632[/TD]
[TD]Bright Stainless Steel[/TD]
[TD]110S81632[/TD]
[TD]Bright Stainless Steel[/TD]
[TD]32[/TD]
[/TR]
[TR]
[TD]110S81632D[/TD]
[TD]Satin Stainless Steel[/TD]
[TD]110S81632D[/TD]
[TD]Satin Stainless Steel[/TD]
[TD]32D[/TD]
[/TR]
[TR]
[TD]110S8164[/TD]
[TD]Satin Brass[/TD]
[TD]110S8164[/TD]
[TD]Satin Brass[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

What formula are you using in C?