Value based on partial match

danwxm

New Member
Joined
Sep 16, 2014
Messages
3
Hi all,

New here and have a question regarding a price update I am trying to do.

I can perform a vlookup on a supplier price update to update the prices of the sub SKUs I have but because the Parent SKUs are slightly different in the sense that they just end with '-P' I cannot get these to update prices. Is there any way I can kind of tell excel that if the SKU ends in '-P' then to look for the first part of the SKU and return the same value as that? Please see the attached worksheet for an example.

SKURRPSale PriceCost PriceSupplier Code
ABC-00001-848-S

<tbody>
</tbody>
10.00
5.002.00
00001848S

<tbody>
</tbody>
ABC-00001-848-M

<tbody>
</tbody>
10.005.002.00
00001848M

<tbody>
</tbody>
ABC-00001-848-L

<tbody>
</tbody>
10.005.002.00
00001848L

<tbody>
</tbody>
ABC-00001-P

<tbody>
</tbody>
9.005.000.00
00001P

<tbody>
</tbody>
ABC-RH-000052-001-S

<tbody>
</tbody>
20.0010.005.00
RH000052001S

<tbody>
</tbody>
ABC-RH-000052-001-M

<tbody>
</tbody>
20.0010.005.00
RH000052001M

<tbody>
</tbody>
ABC-RH-000052-P

<tbody>
</tbody>
19.0010.000.00
RH000052P

<tbody>
</tbody>
ABC-RH-000052-001-L

<tbody>
</tbody>
20.0010.005.00
RH000052001L

<tbody>
</tbody>
ABC-RH-000052-001-XL

<tbody>
</tbody>
20.0010.005.00
RH000052001XL

<tbody>
</tbody>
ABC-RH-000052-001-XXL

<tbody>
</tbody>
20.0010.005.00
RH000052001XXL

<tbody>
</tbody>

<tbody>
</tbody>

As you can see from the above the parent SKUs (ending in -P) have a different price to the sub SKUs because the vlookup used in the RRP column cannot find the SKU in the Supplier Code field as it is lacking the colour indicator (848 or 001) found in the supplier SKU.

Hopefully this all makes sense but if not then please ask me for more information!

Dan
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,066
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Can you give sample data of the sheet where the RPP is looked up from or the vlookup formula that returns the RPP
 

danwxm

New Member
Joined
Sep 16, 2014
Messages
3
Hi both,

I am unable to attach a file :( so I will build another table with the sample supplier price sheet that the previous table is looked up against

Supplier CodeTrade Price
00001848S

<tbody>
</tbody>
2.00
00001848M

<tbody>
</tbody>
2.00
00001848L

<tbody>
</tbody>
2.00
RH000052001S

<tbody>
</tbody>
5.00

<tbody>
</tbody>
RH000052001M

<tbody>
</tbody>
5.00

<tbody>
</tbody>
RH000052001L

<tbody>
</tbody>
5.00

<tbody>
</tbody>
RH000052001XL

<tbody>
</tbody>
5.00

<tbody>
</tbody>
RH000052001XXL

<tbody>
</tbody>
5.00

<tbody>
</tbody>

<tbody>
</tbody>

As I said above, the previous table has vlookups set to find the cost price from the above table and then a formula working out the RRP and sale price from the cost price field.

The problem I have is that the parent SKUs will never be found in the supplier price update sheets as there is no further information other than Product Code (The SKUs are built as Supplier Code-Product Code-Colour Code-Size)

Dan
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

Is this what you want?

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">SKU</td><td style=";">RRP</td><td style=";">Sale Price</td><td style=";">Cost Price</td><td style=";">Supplier Code</td><td style="text-align: right;;"></td><td style=";">Supplier Code</td><td style=";">Trade Price</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">ABC-00001-848-S</td><td style="text-align: right;;">10</td><td style="text-align: right;;">5</td><td style="text-align: right;;">2</td><td style=";">00001848S</td><td style="text-align: right;;"></td><td style=";">00001848S</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">ABC-00001-848-M</td><td style="text-align: right;;">10</td><td style="text-align: right;;">5</td><td style="text-align: right;;">2</td><td style=";">00001848M</td><td style="text-align: right;;"></td><td style=";">00001848M</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">ABC-00001-848-L</td><td style="text-align: right;;">10</td><td style="text-align: right;;">5</td><td style="text-align: right;;">2</td><td style=";">00001848L</td><td style="text-align: right;;"></td><td style=";">00001848L</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">ABC-00001-P</td><td style="text-align: right;;">9</td><td style="text-align: right;;">5</td><td style="text-align: right;;">2</td><td style=";">00001P</td><td style="text-align: right;;"></td><td style=";">RH000052001S</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">ABC-RH-000052-001-S</td><td style="text-align: right;;">20</td><td style="text-align: right;;">10</td><td style="text-align: right;;">5</td><td style=";">RH000052001S</td><td style="text-align: right;;"></td><td style=";">RH000052001M</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">ABC-RH-000052-001-M</td><td style="text-align: right;;">20</td><td style="text-align: right;;">10</td><td style="text-align: right;;">5</td><td style=";">RH000052001M</td><td style="text-align: right;;"></td><td style=";">RH000052001L</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">ABC-RH-000052-P</td><td style="text-align: right;;">19</td><td style="text-align: right;;">10</td><td style="text-align: right;;">5</td><td style=";">RH000052P</td><td style="text-align: right;;"></td><td style=";">RH000052001XL</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">ABC-RH-000052-001-L</td><td style="text-align: right;;">20</td><td style="text-align: right;;">10</td><td style="text-align: right;;">5</td><td style=";">RH000052001L</td><td style="text-align: right;;"></td><td style=";">RH000052001XXL</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">ABC-RH-000052-001-XL</td><td style="text-align: right;;">20</td><td style="text-align: right;;">10</td><td style="text-align: right;;">5</td><td style=";">RH000052001XL</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">ABC-RH-000052-001-XXL</td><td style="text-align: right;;">20</td><td style="text-align: right;;">10</td><td style="text-align: right;;">5</td><td style=";">RH000052001XXL</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D2</th><td style="text-align:left">=VLOOKUP(<font color="Blue">IF(<font color="Red">RIGHT(<font color="Green">E2,1</font>)="P",LEFT(<font color="Green">E2,LEN(<font color="Purple">E2</font>)-1</font>)&"*",E2</font>),G$2:H$9,2,FALSE</font>)</td></tr></tbody></table></td></tr></table><br />
 

danwxm

New Member
Joined
Sep 16, 2014
Messages
3
Is this what you want?

Excel 2010
ABCDEFGH
1SKURRPSale PriceCost PriceSupplier CodeSupplier CodeTrade Price
2ABC-00001-848-S105200001848S00001848S2
3ABC-00001-848-M105200001848M00001848M2
4ABC-00001-848-L105200001848L00001848L2
5ABC-00001-P95200001PRH000052001S5
6ABC-RH-000052-001-S20105RH000052001SRH000052001M5
7ABC-RH-000052-001-M20105RH000052001MRH000052001L5
8ABC-RH-000052-P19105RH000052PRH000052001XL5
9ABC-RH-000052-001-L20105RH000052001LRH000052001XXL5
10ABC-RH-000052-001-XL20105RH000052001XL
11ABC-RH-000052-001-XXL20105RH000052001XXL

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

Worksheet Formulas
CellFormula
D2=VLOOKUP(IF(RIGHT(E2,1)="P",LEFT(E2,LEN(E2)-1)&"*",E2),G$2:H$9,2,FALSE)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Where ABC-00001-848-L and ABC-00001-P are effectively the same product I want the RRP to be the same price rather than in this example the parent being a pound less than the sub SKUs if that makes sense? and at the same time the cost price on the parent SKU to stay as £0.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,118
Messages
5,599,817
Members
414,341
Latest member
Mohammedsobhey

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
Top