Visual Basic Macro for Optimisation Task

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.

Taidot: tietojenkäsittely, Excel, Visual Basic

Näytä lisää: visual basic macro, visual basic x, vb creation, use case creation, set of pairs, find the solution for, find a solution for, brute force solution, Visual Basic Excel, task number 1 project, task allocation, fewest, excel visual basic, compare spreadsheet, work allocation macro, excel macro combination, combinations macro, excel 2013 version, combination program visual basic, excel macro match data, order processing require, range macro excel, combinations visual basic, list combination excel, combinations excel macro

Tietoa työnantajasta:
( 0 arvostelua ) Melbourne, Australia

Projektin tunnus: #4277915

17 freelanceria on tarjonnut keskimäärin 105 $ tähän työhön


Hello, Excel VBA expert here. Please check PM for details. Thanks!

150 $ AUD 3 päivässä
(64 arvostelua)

Hi..I have looked at spreadsheet in detail and have few points to ask.

150 $ AUD 2 päivässä
(78 arvostelua)

Dear dcheetham, I think I have a good solution for you. Please have a look on my example. Best regards

150 $ AUD 1 päivässä
(25 arvostelua)

Hi, I have worked on creating specific Excel applications for 20 years and have completed several freelancer projects, PLEASE CHECK FEEDBACK. Example will follow. I am UK based, on GMT time, available on skype and a na Lisää

150 $ AUD 3 päivässä
(23 arvostelua)

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.

60 $ AUD 1 päivässä
(15 arvostelua)

This is an interesting project. Done a similar one last month. You will need GA algorithms to find the best solution. More info in PM.

100 $ AUD 1 päivässä
(13 arvostelua)

Hi, your project sounds very interesting for me. I am specialized in excel macros, have just finished one... KR,

149 $ AUD 3 päivässä
(18 arvostelua)

Hello we ca do it VB6.0 insteade of Macro with Excell as Back end

60 $ AUD 7 päivässä
(10 arvostelua)

Ready to start right now..!!

100 $ AUD 2 päivässä
(8 arvostelua)

Why pick me? Working solution provided. Please see PM.

150 $ AUD 7 päivässä
(6 arvostelua)

New freelancer, experienced excel worker, I would like to solve the required automation. See PM, please.

50 $ AUD 3 päivässä
(1 arvostelu)

Hi! Project is clear and I can help you out. Actually, I have done similar project recently, check out pm for reference.

70 $ AUD 3 päivässä
(1 arvostelu)

Expert programmer in C/C++/C#/Java/PHP

30 $ AUD 1 päivässä
(1 arvostelu)

I can do this process within a give time of you.I am having 5 years experience in data entry project are web research, typing jobs,excel work.I am ready to help for your project right [url removed, login to view] tel the futher details Lisää

40 $ AUD 2 päivässä
(0 arvostelua)

I fully understand your requirements and it would be a pleasure to provide any help you need.

120 $ AUD 3 päivässä
(0 arvostelua)

I could help you with the project

140 $ AUD 5 päivässä
(0 arvostelua)

Please give us a chance to show the expertise we have in VBA.

120 $ AUD 3 päivässä
(0 arvostelua)