Refer to a simple 5 column representative inventory dataset of a Glass manufacturer:
| Model | Length (MM) | Wide (MM) | Thk (MM) | CAT |
| HX9-G-ARD | 1071 | 273 | 3.5 | A |
| MYP-G-3RD | 580 | 535 | 3.2 | B |
| EPO-G-3RD | 580 | 535 | 3.2 | A |
| MYG-G-3R | 966 | 350 | 3.2 | A |
| MYN-G-3RD | 649 | 530 | 3.2 | A |
| GM SPIN-G-3FD | 882 | 395 | 3.2 | A |
| MY8-G-AR | 880 | 400 | 3.5 | B |
| GM2-G-AR | 880 | 400 | 3.5 | A |
From this inventory data, one has to furnish customer orders based on specific dimensions demanded by them. A typical Customer request would be to supply glass sheets as per the following dimensions
| Length (MM) | Wide (MM) | Thk (MM) | CAT |
| 780 | 542 | 3.5 | A |
The firm may or may not have glass sheets of this specific size. The objective is to identify glass sheets, from the inventory on hand, which match customer specifications. If there is no exact match, then one must be able to obtain all inventory items which have the same Thk (MM) and CAT as the customer specified dimensions but the Length and thickness should be more than equal to the customer specified dimensions. The length and width can then be trimmed to match the exact customer dimensions. Furthermore, the result returned should:
- List only the Top 30 glass sheets available in inventory; and
- List those Top 30 glass sheets in ascending order of wastage (wastage caused when the glass sheet is trimmed to match the customer specified dimensions)
You may refer to my solution in this workbook. I have shared two solutions – one using Excel formulas and the other using Power Query a.k.a. Get and Transform in Excel 2016. Please read the Comments in cells F1, J9 and J16 of the “Solutions” worksheet. The difference between the 2 solutions is:
- Formula driven solution – This is in range J10:AM14 of the Solutions worksheet. This is a semi dynamic solution (as compared to the Power Query solution). To get the models in ascending order of wastage, one will have to create an Area column in the base data and sort that column in ascending order.
- Power Query solution – This is in range J17:AM21 of the Solutions worksheet. This is a dynamic solution. Just change the customer specified dimensions in range G2:J2 of the Data and Query worksheet. Thereafter just right click on any cell in the range below and select refresh.
Return best possible fit, to manually entered dimensions, with the intent to minimise wastage
{ 5 Comments }