# 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.

Skills: Data Processing, Excel, Visual Basic

( 0 reviews ) Melbourne, Australia

Project ID: #4277915

## 17 freelancers are bidding on average \$105 for this job

bharanisrihari

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

\$150 AUD in 3 days
(64 Reviews)
6.5
mrbrave

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

\$150 AUD in 2 days
(78 Reviews)
5.4
pierreho

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

\$150 AUD in 1 day
(25 Reviews)
4.9
MDavidCrompton

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 More

\$150 AUD in 3 days
(23 Reviews)
4.9
satsin7

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 in 1 day
(15 Reviews)
4.5
Tablassus

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 in 1 day
(13 Reviews)
4.3
seaanddream

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

\$149 AUD in 3 days
(18 Reviews)
4.0
srdas2012

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

\$60 AUD in 7 days
(10 Reviews)
3.3
jaylancer43

\$100 AUD in 2 days
(8 Reviews)
3.2

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

\$150 AUD in 7 days
(6 Reviews)
2.8
ietim

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

\$50 AUD in 3 days
(1 Review)
1.7
vedranvivoda

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

\$70 AUD in 3 days
(1 Review)
1.6
JeffLee21

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

\$30 AUD in 1 day
(1 Review)
1.4
eurosofthelp

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

\$120 AUD in 3 days
(0 Reviews)
0.0
oobretenov

\$140 AUD in 5 days
(0 Reviews)
0.0
workt43

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 now.Please tel the futher details More

\$40 AUD in 2 days
(0 Reviews)
0.0
irenemaganari

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

\$120 AUD in 3 days
(0 Reviews)
0.0