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 %project_bid_stats_avg_sub_26% %project_currencyDetails_sign_sub_27% 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 [login to view URL] 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)