Footwear company receives orders with quantities of each size that they want. For example: Product A - 2 x Size 36, 4 x Size 37, 8 x Size 38, 9 x Size 39, 7 x Size 40, 3 x Size 41, 0 x Size 42 - Total 33.
When placing orders from the factories, there are set packs that we have to use. For example, our F-version 9 pair pack (9F) has the size range: 1 x Size 36, 2 x Size 37, 2 x Size 38, 2 x Size 39, 1 x Size 40, 1 x Size 41, 0 x Size 42; while our our C-version 12 pair pack (12C) has the size range: 0 x Size 36, 1 x Size 37, 2 x Size 38, 3 x Size 39, 3 x Size 40, 2 x Size 41, 1 x Size 42. We have two or three versions of each of 6 pair, 9 pair, 12 pair, and 18 pair packs.
The project requires the creation of an Excel spreadsheet document including a VB macro that will take an input of the order received from the customer (will be a number for each size 36, 37, 38, 39, 40, 41, 42), which will then be compared to a list of available packs (there will be anywhere between 5 and 20 pack options, which are able to be inputted by the user). The program will compare the customer order to the available packs, and find the best combination of available packs to either exactly match the order or have the fewest discrepancies possible).
The customer order will normally have a total pair amount that is able to be made up by some combination of pack sizes (eg. 33 total pairs can be made up by 3 x 9 pair packs and a 6 pair pack, 4 x 6 pair packs and a 9 pair pack, or 1 x 18 pair pack, 1 x 9 pair pack and a 6 pair pack), but the program needs to be able to still find the most optimal solution even if this is not the case.
The output of the program should be either:
a. If a perfect solution is avaliable, the packs that make this up should be presented (eg. 3 x 9F, 2 x 12C)
b. If a perfect solution is not available, the pack combination(s) that have the least number of pairs out of place should be presented, with a way to read what the discrepancies are. eg. If there are two combinations that have 2 pairs out of place, they should both be listed with details of which sizes allocation will be overstocked and which will be understocked.
Attached is an excel spreadsheet that shows the shows the kind of packs that we work with. The attached file shows my brute force method for working on orders of exactly 24 pairs, whereupon I realised that I was out of my depth and needed some professional help.
Please PM me if you require further information.
17 freelancers are bidding on average $105 for this job
Hi I have more than 15 years experience in using Excel adn automating using advanced formula, add-ins and macros. I already found solution to your interesting problem. Pls see PMB for details.