Ordering system - least number of shipments

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.

>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 :)

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?
 

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

>There is no preference for one or the other.
Meaning - I do not prefer any one warehouse to another (A,B,C)

Where is your data bein maintained, in Access or a SQL server of some kind?