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.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]SKU[/TD]
[TD]RRP[/TD]
[TD]Sale Price[/TD]
[TD]Cost Price[/TD]
[TD]Supplier Code[/TD]
[/TR]
[TR]
[TD][TABLE="width: 154"]
<tbody>[TR]
[TD="class: xl63, width: 154"]ABC-00001-848-S[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]10.00
[/TD]
[TD]5.00[/TD]
[TD]2.00[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="class: xl65, width: 110"]00001848S[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 154"]
<tbody>[TR]
[TD="class: xl65, width: 154"]ABC-00001-848-M[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]10.00[/TD]
[TD]5.00[/TD]
[TD]2.00[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="class: xl65, width: 110"]00001848M[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 154"]
<tbody>[TR]
[TD="class: xl65, width: 154"]ABC-00001-848-L[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]10.00[/TD]
[TD]5.00[/TD]
[TD]2.00[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="class: xl65, width: 110"]00001848L[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 154"]
<tbody>[TR]
[TD="class: xl65, width: 154"]ABC-00001-P[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]9.00[/TD]
[TD]5.00[/TD]
[TD]0.00[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="class: xl65, width: 110"]00001P[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 154"]
<tbody>[TR]
[TD="class: xl65, width: 154"]ABC-RH-000052-001-S[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]20.00[/TD]
[TD]10.00[/TD]
[TD]5.00[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="class: xl65, width: 110"]RH000052001S[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 154"]
<tbody>[TR]
[TD="class: xl65, width: 154"]ABC-RH-000052-001-M[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]20.00[/TD]
[TD]10.00[/TD]
[TD]5.00[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="class: xl65, width: 110"]RH000052001M[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 154"]
<tbody>[TR]
[TD="class: xl65, width: 154"]ABC-RH-000052-P[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]19.00[/TD]
[TD]10.00[/TD]
[TD]0.00[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="class: xl65, width: 110"]RH000052P[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 154"]
<tbody>[TR]
[TD="class: xl65, width: 154"]ABC-RH-000052-001-L[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]20.00[/TD]
[TD]10.00[/TD]
[TD]5.00[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="class: xl65, width: 110"]RH000052001L[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 154"]
<tbody>[TR]
[TD="class: xl65, width: 154"]ABC-RH-000052-001-XL[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]20.00[/TD]
[TD]10.00[/TD]
[TD]5.00[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="class: xl65, width: 110"]RH000052001XL[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 154"]
<tbody>[TR]
[TD="class: xl65, width: 154"]ABC-RH-000052-001-XXL[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]20.00[/TD]
[TD]10.00[/TD]
[TD]5.00[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="class: xl65, width: 110"]RH000052001XXL[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
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
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.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]SKU[/TD]
[TD]RRP[/TD]
[TD]Sale Price[/TD]
[TD]Cost Price[/TD]
[TD]Supplier Code[/TD]
[/TR]
[TR]
[TD][TABLE="width: 154"]
<tbody>[TR]
[TD="class: xl63, width: 154"]ABC-00001-848-S[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]10.00
[/TD]
[TD]5.00[/TD]
[TD]2.00[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="class: xl65, width: 110"]00001848S[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 154"]
<tbody>[TR]
[TD="class: xl65, width: 154"]ABC-00001-848-M[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]10.00[/TD]
[TD]5.00[/TD]
[TD]2.00[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="class: xl65, width: 110"]00001848M[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 154"]
<tbody>[TR]
[TD="class: xl65, width: 154"]ABC-00001-848-L[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]10.00[/TD]
[TD]5.00[/TD]
[TD]2.00[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="class: xl65, width: 110"]00001848L[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 154"]
<tbody>[TR]
[TD="class: xl65, width: 154"]ABC-00001-P[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]9.00[/TD]
[TD]5.00[/TD]
[TD]0.00[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="class: xl65, width: 110"]00001P[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 154"]
<tbody>[TR]
[TD="class: xl65, width: 154"]ABC-RH-000052-001-S[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]20.00[/TD]
[TD]10.00[/TD]
[TD]5.00[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="class: xl65, width: 110"]RH000052001S[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 154"]
<tbody>[TR]
[TD="class: xl65, width: 154"]ABC-RH-000052-001-M[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]20.00[/TD]
[TD]10.00[/TD]
[TD]5.00[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="class: xl65, width: 110"]RH000052001M[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 154"]
<tbody>[TR]
[TD="class: xl65, width: 154"]ABC-RH-000052-P[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]19.00[/TD]
[TD]10.00[/TD]
[TD]0.00[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="class: xl65, width: 110"]RH000052P[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 154"]
<tbody>[TR]
[TD="class: xl65, width: 154"]ABC-RH-000052-001-L[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]20.00[/TD]
[TD]10.00[/TD]
[TD]5.00[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="class: xl65, width: 110"]RH000052001L[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 154"]
<tbody>[TR]
[TD="class: xl65, width: 154"]ABC-RH-000052-001-XL[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]20.00[/TD]
[TD]10.00[/TD]
[TD]5.00[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="class: xl65, width: 110"]RH000052001XL[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 154"]
<tbody>[TR]
[TD="class: xl65, width: 154"]ABC-RH-000052-001-XXL[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]20.00[/TD]
[TD]10.00[/TD]
[TD]5.00[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="class: xl65, width: 110"]RH000052001XXL[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
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