Create a list of base sku's or product groups

P

Plank22

New Member
Joined
Apr 7, 2016
Messages
36
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
You cannot attach files to this board, but there are some tools here that enable you to post sample data
 
Upvote 0
[TABLE="width: 456"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]100T31210[/TD]
[TD]Satin Bronze[/TD]
[TD]100T312[/TD]
[/TR]
[TR]
[TD]100T31210B[/TD]
[TD]Oil Rubbed Bronze[/TD]
[TD]100T3121[/TD]
[/TR]
[TR]
[TD]100T3121510[/TD]
[TD]Satin Bronze[/TD]
[TD]100T31215[/TD]
[/TR]
[TR]
[TD]100T3121510B[/TD]
[TD]Oil Rubbed Bronze[/TD]
[TD]100T31215[/TD]
[/TR]
[TR]
[TD]100T3121526[/TD]
[TD]Bright Chrome[/TD]
[TD]100T31215[/TD]
[/TR]
[TR]
[TD]100T3121526D[/TD]
[TD]Satin Chrome[/TD]
[TD]100T31215[/TD]
[/TR]
[TR]
[TD]100T3121528[/TD]
[TD]Aluminum[/TD]
[TD]100T31215[/TD]
[/TR]
[TR]
[TD]100T312153[/TD]
[TD]Bright Brass[/TD]
[TD]100T31215[/TD]
[/TR]
[TR]
[TD]100T3121532[/TD]
[TD]Bright Stainless Steel[/TD]
[TD]100T31215[/TD]
[/TR]
[TR]
[TD]100T3121532D[/TD]
[TD]Satin Stainless Steel[/TD]
[TD]100T31215[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You could do something like this:

ABCDEF
SKUColorBase SKUColorCode

<colgroup><col style="width: 25pxpx"><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[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Satin Bronze[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]100T312[/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="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]100T312[/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="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]100T31215[/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="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]100T31215[/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="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]100T31215[/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="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]100T31215[/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="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]100T31215[/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="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]100T31215[/TD]
[TD="align: right"][/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="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]100T31215[/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="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]100T31215[/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(LEFT(A2,LEN(A2)-LEN(VLOOKUP(B2,$E$2:$F$9,2,0))),A2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Currently we do not have the E and F columns as you have them and would nee to populate them based on column B.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Create a 2 column sheet that moves the selected cell along then down when filled
Replies
2
Views
340
R11989
R
J
  • Question Question
A VBA Code to Transfer rows of cells across Sheets based on Certain Column Value
Replies
2
Views
116
Peter_SSs
Peter_SSs
S
  • Question Question
Compressing large amount of rows but adding totals?
Replies
6
Views
119
jkpieterse
jkpieterse
R
  • Question Question
creating a dynamic list based on values in a table
Replies
3
Views
770
rwells
R
G
  • Question Question
Green Triangles in Tables - how to fix?
Replies
4
Views
422
gooniegirl180
G

Forum statistics

Threads
1,223,895
Messages
6,175,257
Members
452,625
Latest member
saadat28

Share this page

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