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.

 

[mrshl9898]

How's this?

=IFERROR(VLOOKUP(E2,A:D,4,0)/(MID(VLOOKUP(E2,A:C,3,0),FIND("X",VLOOKUP(E2,A:C,3,0))+1,4)*MID(VLOOKUP(E2,A:C,3,0),FIND(" ",VLOOKUP(E2,A:C,3,0))+1,FIND("X",VLOOKUP(E2,A:C,3,0))-FIND(" ",VLOOKUP(E2,A:C,3,0))-1)),"")

 

[solidENM]

Hi Mrshl, thanks for the reply but im not seeing anything popup after this is inserted. I tried altering it a bit to see if the columns had anything to do with it. Column a is the condensed part#, and column b is spaced apart for better readability (setup as =replace from column a). Im not sure if thats throwing a wrench in your code.

 

[Snackan1986]

Vlookup(e5;$b$2:$d$4;3;false)/right(vlookup(e5;$b$2:$c$4;2;false);(len(vlookup(e5;$b$2:$c$4;2;false))-search("x";vlookup(e5;$b$2:$c$4;2;false);1)))/mid(vlookup(e5;$b$2:$c$4;2;false);find(" ";vlookup(e5;$b$2:$c$4;2;false);1)+1;(search("x";vlookup(e5;$b$2:$c$4;2;false);1)-find(" ";vlookup(e5;$b$2:$c$4;2;false);1)-1))

 

[mrshl9898]

As long as the part number 5698552362 is in A, yellow 20X5600 is in C and 4312.25 is in D that should get you the result.

What is an example of what is in B?

 

[solidENM]

=REPLACE(REPLACE(REPLACE(A3504,8,0," "),5,0," "),2,0," ")

this is an example from B column
takes A and adds spaces.
5698552384 turns into 5 698 552 384

 

[mrshl9898]

Sorry mate, a bad copy and paste on my behalf (I had this in Column F originally).

In D5

=IFERROR(VLOOKUP(E5,A:D,4,0)/(MID(VLOOKUP(E5,A:C,3,0),FIND("X",VLOOKUP(E5,A:C,3,0))+1,4)*MID(VLOOKUP(E5,A:C,3,0),FIND(" ",VLOOKUP(E5,A:C,3,0))+1,FIND("X",VLOOKUP(E5,A:C,3,0))-FIND(" ",VLOOKUP(E5,A:C,3,0))-1)),"")

 

[solidENM]

Vlookup(e5;$b$2:$d$4;3;false)/right(vlookup(e5;$b$2:$c$4;2;false);(len(vlookup(e5;$b$2:$c$4;2;false))-search("x";vlookup(e5;$b$2:$c$4;2;false);1)))/mid(vlookup(e5;$b$2:$c$4;2;false);find(" ";vlookup(e5;$b$2:$c$4;2;false);1)+1;(search("x";vlookup(e5;$b$2:$c$4;2;false);1)-find(" ";vlookup(e5;$b$2:$c$4;2;false);1)-1))

Hi Snackan, thanks for the help. I am getting popup errors with it. excel is saying "we found a problem with this formula" and its above my head, so im not even sure where to begin in an attempt to troubleshoot.

 

[solidENM]

Sorry mate, a bad copy and paste on my behalf (I had this in Column F originally).

In D5

=IFERROR(VLOOKUP(E5,A:D,4,0)/(MID(VLOOKUP(E5,A:C,3,0),FIND("X",VLOOKUP(E5,A:C,3,0))+1,4)*MID(VLOOKUP(E5,A:C,3,0),FIND(" ",VLOOKUP(E5,A:C,3,0))+1,FIND("X",VLOOKUP(E5,A:C,3,0))-FIND(" ",VLOOKUP(E5,A:C,3,0))-1)),"")

No errors on this one, but im not getting any output from it. It appears to be searching the correct columns, but im not sure why its coming up blank. I posted the code in column F ( empty column) and also tried in d in case you intended it to work in there.

 

[mrshl9898]

https://ibb.co/mqksNb

Is this what you are seeing?

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