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.
<tbody>
</tbody>
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.
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.
| row | Column a | Column C | Column D | Column E |
| Part Number | Description | Price | Bulk # (if appl) | |
| 1 | 5698552362 | yellow 20X5600 | 4312.25 | |
| 2 | 5698552373 | Green 20X6000 | 1945.25 | |
| 3 | 5698552384 | red 20X6070 | 2525.12 | |
| 4 | 6574552345 | yellow piece | 0.038502 | 5698552362 |
| 5 | 6574552356 | green piece | 5698552373 | |
| 6 | 6574552367 | red piece | 5698552384 |
<tbody>
</tbody>
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.
Last edited:
