Solved

Ordering system - least number of shipments

Posted on 2004-09-22
7
282 Views
Last Modified: 2010-04-17
OK - here is a good one.  I am making an ordering system that needs to figure out the best way to place orders. Let's say we have products 1-9 that need a specific QTY (N) from houses A-C.

   N  A  B  C
1  4  3  2  0
2  1  0  1  0
3  3  0  1  1
4  7  2  0  3
5  3  0  0  7
6  8  4  3  4
7  5  0  0  4
8  6  0  6  2
9  8  6  9  4


Anyone know of an existing solution to this - I have been banging my head against the wall forever - there are TONS of problems in here.

I am doing this in ASP/VBScript so that would be a preferable solution but I will take ANYTHING.

I had asked a similiar question a long time ago - but it did not take the needed QTY into account and that changes everything.

Thanks everyone!
0
Comment
Question by:SpreadTheWord
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 57

Expert Comment

by:Julian Hansen
ID: 12131235
It is not 100% clear from your question how you want to solve this problem so I am going to make an assumption about how I understand the problem and we can go from there.

I am assuming that

i) All 9 products must be ordered
ii) That only N products must be ordered per product (that one is obvious)
iii) That we can fill the order from any of the houses A-C
iv) Data is stored in a 2 dimensional array
v) Arrays are 1 based

The simplest solution I can see is to allocate from houses successively until the quantity N is satisfied.
Dim product() ' Stores the required product quantity
Dim house() ' Stores the product number and quantity available
Dim houseOrder() ' Stores the amount to order from each house for each product

For prod = 1 to 9 (or variable holding number of products in the generic case)
  QtyReq = product(prod)
  indx = 1
  do while order > 0
    order = Min(QtyReq, house(prod, indx)
    QtyReq = QtyReq - order
    houseOrder(prod, indx) = order
    indx = indx + 1
  loop
next

This should leave the arrah houseOrder with the amounts that you want to order from each house.

That is the logic - have not tried to translate it into VBScript - not sure if you need help with the coding or just the algorithm.
0
 

Author Comment

by:SpreadTheWord
ID: 12132593
>All 9 products must be ordered
Not always - if there is none in stock at any warehouse than none will be ordered - but it should order the max number available from all warehouses if it cannot fill it all in one warehouse.

>That only N products must be ordered per product (that one is obvious)
Yes

>That we can fill the order from any of the houses A-C
Yes - but through the minimum number of warehouses - since shipping charges will increase if more are used.  If you MUST use all 3 than it is allowed - if it can be done through 1 or 2 than that is better.

>Data is stored in a 2 dimensional array
Yes - but can be changed.

>Arrays are 1 based
0 based - but can be changed.

Your solution works - but it does not try for optimization.  Let's say the Array looked like this:

   N  A  B  C
1  5  4  1  5
2  3  0  0  3

Than the best ordering warehouse is C since both can be shipped from there in full.

Make more sense? Sorry - I am not good at explaining things :)
0
 
LVL 57

Expert Comment

by:Julian Hansen
ID: 12132907
No problem - identifying the problem is the first step to finding a solution.

I think I understand the problem better now and it is a very nice problem.

Ok, some more questions

1. can we assume only 3 warehouses or must this solution extend to the general case.
2. Given the following situation

   N  A  B  C
1  5  4  1  5
2  3  0  3  0
3  4  4  0  1

In this scenario there is no option but to order something from everybody. In this case is there a cost difference between

1 C = 5
2 B = 3
3 A = 4

And

1 A=4 C=1
2 B=3
3 A=4

Or
1 C=5
2 B=3
3 A=3 and C=1

What would be the best option based on the above data?
 
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:SpreadTheWord
ID: 12134686
>can we assume only 3 warehouses or must this solution extend to the general case.
Currently there are only 3 - and I don't see that changing anytime soon - but of course it would be best to be flexible - even if that requires recoding - just that the logic could extend to more than 3. Then again - if there is a good solution that works for 3 and 3 only - than I will take it and worry about expansion when that time comes.

>What would be the best option based on the above data?
The cost is flat per warehouse - so it does not matter how many products come from that warehouse - only if it is used.  The cost of all of the options you listed would be the same. The best option in that case is:

1 A=4 B=1
2 B=3
3 A=4

That way only A and B are activated. There is no preference for one or the other.
0
 

Author Comment

by:SpreadTheWord
ID: 12134702
>There is no preference for one or the other.
Meaning - I do not prefer any one warehouse to another (A,B,C)
0
 
LVL 5

Accepted Solution

by:
kemp_a earned 500 total points
ID: 12186703
I dont believe solution is that difficult, what you want to do is have the orders placed against the warehouse that can deliver the closest number of goods that matches your order.

As the example goes:

   N  A  B  C
1  5  4  1  5
2  3  0  3  0
3  4  4  0  1

You want to order 12 items, warehouse A has 8, B has 4, C has 4, order 8 from A
You have 4 items remaining, A has 0, B has 4, C has 1 order 4 from B

What do you plan to do if there is not enough stock available to order?

From the original table:
   N  A  B  C
1  4  3  2  0
2  1  0  1  0
3  3  0  1  1
4  7  2  0  3
5  3  0  0  7
6  8  4  3  4
7  5  0  0  4
8  6  0  6  2
9  8  6  9  4

You want to order 45 items, A has 15, B has 21, C has 18, order 21 from B, Table now looks like this:
   N  A  B  C
1  2  3  0  0
2  0  0  0  0
3  2  0  0  1
4  7  2  0  3
5  3  0  0  7
6  5  4  0  4
7  5  0  0  4
8  0  0  0  2
9  0  6  1  4

You now want to order 24 items, A has 8, B has 0, C has 15, order 15 from C, Table now looks like this:
   N  A  B  C
1  2  3  0  0
2  0  0  0  0
3  1  0  0  0
4  4  2  0  0
5  0  0  0  4
6  1  4  0  0
7  1  0  0  0
8  0  0  0  2
9  0  6  1  4

You now want to order 9 items, A has 5, B has 0, C has 0, order 5 from A, Table now looks like this:
   N  A  B  C
1  0  1  0  0
2  0  0  0  0
3  1  0  0  0
4  2  0  0  0
5  0  0  0  4
6  0  3  0  0
7  1  0  0  0
8  0  0  0  2
9  0  6  1  4

In this case, you had to order from all three, and you didnt get everything that you wanted.
Do you not want to order if there isn't enough stock available or do you want to take what stock is available?
0
 
LVL 5

Expert Comment

by:kemp_a
ID: 12187202
Where is your data bein maintained, in Access or a SQL server of some kind?
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question