Need formula to find match, then divide bulk pricing to produce total price per piece.

[solidENM]

Hello,
I have a master list that includes crates and individual piece pricing. When this was originally setup, it was all manual. There are thousands of items and pricing changes do occur. I have received updated pricing for crates, but not individual pieces. I need help creating a formula that will take a crate #, get the quantity from the matching cell, and divide the total price by packs and boxes.

Using the table below, yellow crates in row 1 show there are 20 boxes with 5600 pieces each, total price for all that is 4312.25.
Row 4 has an individual yellow part. Column E shows the matching crate number. I need a formula that will use E4, search column a for a match, then take 4312.25 / 20 / 5600, and then fill in the new price in D4. In this case, its 0.038502

Essentially it will take total price, divide by boxes, by pieces, and produce the each price in column D. each crates description is in the same format, with a #x # at the end of each cell. This is the modifier I need to divide the price in column D.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]row[/TD]
[TD]Column a[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Part Number[/TD]
[TD]Description[/TD]
[TD]Price[/TD]
[TD]Bulk # (if appl)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]5698552362[/TD]
[TD]yellow 20X5600[/TD]
[TD]4312.25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]5698552373[/TD]
[TD]Green 20X6000[/TD]
[TD]1945.25[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]5698552384[/TD]
[TD]red 20X6070[/TD]
[TD]2525.12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]6574552345[/TD]
[TD]yellow piece[/TD]
[TD] 0.038502[/TD]
[TD]5698552362[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]6574552356[/TD]
[TD]green piece[/TD]
[TD][/TD]
[TD]5698552373[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]6574552367[/TD]
[TD]red piece[/TD]
[TD][/TD]
[TD]5698552384[/TD]
[/TR]
</tbody>[/TABLE]


If i can get this formula working, I will only have to type in the bulk part# equivalent for each "piece line" and future updates will go smoothly.


Thanks for looking, and Im hoping you can help.

 

[solidENM]

https://ibb.co/mqksNb

Is this what you are seeing?

Is the formula in row 5? (headers in row 1)

Works now, thanks Mrshl. I had simplified my template to make an easy to read example on here. I pasted the example directly into excel when testing your code, and had the column with Row#s on there.... Thanks for pushing it back and letting me know it works. From here Ill try and modify it so it works in my longer master list.
Thank you for all your help!

 

[mrshl9898]

Glad we got there

 

[Snakehips]

solidENM,

Just be mindful that the above solution will not give the expected result if your description has any other spaces preceding the one before the **X****. Or if there is a preceding "X" in the description.

 

[Snakehips]

Just in case it helps, the below should take care of the issue if preceding spaces are likely to give you a problem. Also it will allow for box and piece quantities to be other than **X****. eg ****X**
Edit the range lower limit from 1000 to a sensible max for your data set.

Excel 2010
	A	B	C	D	E	F
1	Part Number		Description	Price	Bulk # (if appl)	Piece prices
2	5698552362	5 698 552 362	Xtra yellow 20X5600	4312.25
3	5698552373	5 698 552 373	Green 20X6000	1945.25
4	5698552384	5 698 552 384	red 20X6070	2525.12
5	6574552345	6 574 552 345	Xtra yellow piece		5698552362	0.038502
6	6574552356	6 574 552 356	green piece		5698552373	0.016210
7	6574552367	6 574 552 367	red piece		5698552384	0.020800
Sheet3

Cell Formulas
Range	Formula
F2	=IFERROR(VLOOKUP(E2,$A$1:$D$1000,4,0)/RIGHT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(VLOOKUP(E2,$A$1:$C$1000,3,0)," ",REPT(" ",99)),20)),"X",REPT(" ",10)),10)/LEFT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(VLOOKUP(E2,$A$1:$C$1000,3,0)," ",REPT(" ",99)),20)),"X",REPT(" ",10)),10)," ")

Hope that helps.

 

[solidENM]

Thanks everyone for all the help. I appreciate all the replies.