I would like to build a excel template to resolve the following issue: On the left I have bulk pallets going into a production facility and on the right pallets going out. The “letter” have to stay part of the weights/products. However since the pallets going out of the facility have another weight I can not simply take the same letter as the bulk; if bulk is spread over multiple pallets both letters have to be linked to the products leaving the production facility. The column on the far right is what I would like the template to do for me. Can anybody help? Is there a formula to resolve this?
| Pallet IN | Ranging from/to | Pallet OUT | ranging from/to | |||||||
| LETTER | Palletweight | from | to | Palletweight | from | to | Correct new letters | |||
| A | 904 | 0 | 904 | 546 | 0 | 546 | A | |||
| B | 577 | 904 | 1481 | 450 | 546 | 996 | AB | |||
| C | 574 | 1481 | 2055 | 400 | 996 | 1396 | B | |||
| D | 514 | 2055 | 2569 | 582 | 1396 | 1978 | BC | |||
| E | 486 | 2569 | 3055 | 484 | 1978 | 2462 | CD | |||
| F | 548 | 3055 | 3603 | 603 | 2462 | 3065 | DEF | |||
| G | 524 | 3603 | 4127 | 504 | 3065 | 3569 | F | |||
| H | 572 | 4127 | 4699 | 577 | 3569 | 4146 | FGH | |||
| I | 559 | 4699 | 5258 | 574 | 4146 | 4720 | HI | |||
| J | 539 | 5258 | 5797 | 514 | 4720 | 5234 | I |