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?

 

[Fluff]

You cannot attach files to this board, but there are some tools here that enable you to post sample data

 

[Plank22]

100T31210	Satin Bronze	100T312
100T31210B	Oil Rubbed Bronze	100T3121
100T3121510	Satin Bronze	100T31215
100T3121510B	Oil Rubbed Bronze	100T31215
100T3121526	Bright Chrome	100T31215
100T3121526D	Satin Chrome	100T31215
100T3121528	Aluminum	100T31215
100T312153	Bright Brass	100T31215
100T3121532	Bright Stainless Steel	100T31215
100T3121532D	Satin Stainless Steel	100T31215

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[Eric W]

You could do something like this:

	A	B	C	D	E	F
1	SKU	Color	Base SKU		Color	Code
2	100T31210	Satin Bronze	100T312		Satin Bronze	10
3	100T31210B	Oil Rubbed Bronze	100T312		Oil Rubbed Bronze	10B
4	100T3121510	Satin Bronze	100T31215		Bright Chrome	26
5	100T3121510B	Oil Rubbed Bronze	100T31215		Satin Chrome	26D
6	100T3121526	Bright Chrome	100T31215		Aluminum	28
7	100T3121526D	Satin Chrome	100T31215		Bright Brass	3
8	100T3121528	Aluminum	100T31215		Bright Stainless Steel	32
9	100T312153	Bright Brass	100T31215		Satin Stainless Steel	32D
10	100T3121532	Bright Stainless Steel	100T31215
11	100T3121532D	Satin Stainless Steel	100T31215

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula C2 =IFERROR(LEFT(A2,LEN(A2)-LEN(VLOOKUP(B2,$E$2:$F$9,2,0))),A2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Plank22]

Currently we do not have the E and F columns as you have them and would nee to populate them based on column B.

 

[Eric W]

You said that you'd need to edit the formula for each finish. Instead, you can build a table such as the one in E:F, and then you only need to use one formula. Without such a table, there's no way to decide how many digits to remove from the end of the SKU. With only 15 finishes or so, it shouldn't take you too long to build it. Copy column B to E, then use the Remove Duplicates tool. Then you just need to put in the codes.

 

[Plank22]

Hi Eric,

There are over 12500 rows. I was really just looking for one I could edit for each finish.

Example: Place in column c2, If column b2 = satin bronze remove last to digits of column a2. The I could just copy down and edit for the next finish. I that would take less time than adding all the finish codes.

Let me know if I am wrong or missing something here

 

[Eric W]

The formula you seem to be looking for is:

=IF(B2="Satin Bronze",LEFT(A2,LEN(A2)-2),"")

Do you intend on editing that formula 12500 times? That seems like a MUCH bigger task than creating your color table with 15 entries, then doing a cut/paste of the formula to all the output cells one time only.

 

[Plank22]

No sir, I just drug it down then edited the formula. It did work but after reviewing there some skus that the last two digits are not were the color lies in the sku.

Example 127931210.BX would need to be 1279312.BX

Is there a way to edit the formula two only remove the last two digits if they match 10 in this example?

 

[Eric W]

I'm still a bit confused as to how you're doing things, but try:

=IF(B2="Satin Bronze",SUBSTITUTE(A2,"10","",(LEN(A2)-LEN(SUBSTITUTE(A2,"10","")))/LEN("10")),"")

This will remove the last instance of 10 from the SKU.