Minimization of numbers of vendors

**Jowi7991** · 06-18-2012, 05:54 AM

Hi,
I am trying to find a clever way to use Excel to solve an otherwise time consuming task but have failed so far, why I am now looking for some help and ideas.
I have a list of articles available from a bunch of vendors. The aim is simply to order the articles from as few vendors as possible. Should not be too hard, should it?
Any ideas are appreciated.
Regards,
Jowi

**Md Aejaz** · 06-18-2012, 05:57 AM

Dear JOWi ,

it is better if you attach some sample data based on your requirement.
or else we have to do it which you may not understand.

**Jowi7991** · 06-18-2012, 07:41 AM

Dear Md Aejaz,
I apologize for not been clear and I hope this quickly compiled example can be of help (Product A-E are to be purchased from vendors x1-x4). Here I have also included a price, but the initial problem is to minimize the number of vendors.

Product Vendor Price

A x1 10
x3 15

B x2 11
x3 12

C x1 15
x2 10
x4 12

D x3 13
x4 13

E x1 12
x4 10

regards,
Jowi

**Md Aejaz** · 06-18-2012, 08:11 AM

hi

select min.xlsx

check the attatchment .
this file gives the list of Vendors with minimum price.

**Jowi7991** · 06-18-2012, 09:14 AM

Dear Aejaz,
Many thanks. I will try to apply your solution to the minimization of the number of vendors.
Kind regards,
Jowi

**Jowi7991** · 06-18-2012, 09:50 AM

Dear Aejaz,
Many thanks. Unfortunately it seems I have been unclear in my task description. I realize I should not have included the prices. Let me put it this way:
Some products can only be purchased from certain vendors. I would like to minimize the number of vendors (in the first place).
Assume products A-E and vendors x1-x5

A can be bought from x1, x2, and x3
B from x2 and x4
C from x2 and x4
D from x1 and x5
E only from x5

Thus, only two vendors are needed (x2 and x5).
One solution, I guess, would be to make a 2D matrix with all products vs all vendors, indicate availability by a '1' and lack thereof with '0', and solve it visually. However, that would be really impractical with larger data sets!

The problem can be further complicated by the introduction of prices and shipping costs, but as an example I think the problem described above is enough.
Kind regards,
Jowi