Finding best combination for machines

[stzruya]

Hi,
I have a list of machines that can produce diffrent types of products. some machine can produce same part but there is diffrent between machine in the quality performance. also not all macines can produce all products.
I would like to get the best setup in order to set the product that will give me the best quality. also i need number of machines for the same product. so once i'm using machine for product 1 i cant use it to product 2 and so on....

Hope i manage to explain what i'm looking for.

Appriciate your support

Sam

****************************************************************************

below is example of the data i have:



[TABLE="width: 665"]
<colgroup><col><col span="6"></colgroup><tbody>[TR]
[TD]# of Machines for each product[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Machine[/TD]
[TD]Product #1[/TD]
[TD]Product #2[/TD]
[TD]Product #3[/TD]
[TD]Product #4[/TD]
[TD]Product #5[/TD]
[TD]Product #6[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]N/A[/TD]
[TD]72.6%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]83.5%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]88.3%[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]82.7%[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]N/A[/TD]
[TD]85.3%[/TD]
[TD]N/A[/TD]
[TD]85.9%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]89.0%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]80.8%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]72.8%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]78.5%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]83.1%[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]N/A[/TD]
[TD]78.0%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]80.5%[/TD]
[TD]70.5%[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]N/A[/TD]
[TD]82.5%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]81.0%[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]80.2%[/TD]
[TD]84.4%[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]86.7%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]80.1%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]N/A[/TD]
[TD]82.6%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]82.8%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]82.0%[/TD]
[TD]77.0%[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]N/A[/TD]
[TD]83.2%[/TD]
[TD]N/A[/TD]
[TD]82.0%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]77.7%[/TD]
[TD]80.9%[/TD]
[TD]78.1%[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]N/A[/TD]
[TD]73.7%[/TD]
[TD]N/A[/TD]
[TD]78.8%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
</tbody>[/TABLE]

 

[shg]

Depends on the demand for each product, no?

Otherwise, just use every machine for what it's best at making.

 

[stzruya]

Yes, It is dpend on the demand. Number of machines per product will be chaned according to demand.
Is there a way that excel will generate best combination while taking into account the reuqiured number of machine and set the best quality for the product.
Doing it manually can lead to mistake and sometimes to wrong combinations - i need all amchines to work at the same time...

 

[stzruya]

Hi,
I'm trying to explain what i need in a better way.
I have a list of machines and list of products, not all machines can produce all products, quality perofrmance for each machine and for specific product is diffrent. I have a demand plan and i want to find the best combination for planning the machins with the best quality peroformance. For each product i have specific demand. allso i want to get minimum change over between products on the same machines if it's possible.

My table looks like that:

[TABLE="width: 979"]
<colgroup><col><col span="12"></colgroup><tbody>[TR]
[TD]Machine #[/TD]
[TD]Product #1[/TD]
[TD]Product #2[/TD]
[TD]Product #3[/TD]
[TD]Product #4[/TD]
[TD]Product #5[/TD]
[TD]Product #6[/TD]
[TD]Product #7[/TD]
[TD]Product #8[/TD]
[TD]Product #9[/TD]
[TD]Product #10[/TD]
[TD]Product #11[/TD]
[TD]Product #12[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]N/A[/TD]
[TD]72.6%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]83.5%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]88.3%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]89.4%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]82.7%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]86.4%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]N/A[/TD]
[TD]85.3%[/TD]
[TD]N/A[/TD]
[TD]85.9%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]88.2%[/TD]
[TD]96.2%[/TD]
[TD]84.8%[/TD]
[TD]96.6%[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]89.0%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]80.8%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]72.8%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]78.5%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]83.1%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]84.1%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]N/A[/TD]
[TD]78.0%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]80.5%[/TD]
[TD]70.5%[/TD]
[TD]N/A[/TD]
[TD]80.0%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]N/A[/TD]
[TD]82.5%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]81.0%[/TD]
[TD]80.1%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]80.2%[/TD]
[TD]84.4%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]89.0%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]86.7%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]80.1%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]92.6%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]N/A[/TD]
[TD]82.6%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]82.8%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]77.4%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]82.0%[/TD]
[TD]77.0%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]N/A[/TD]
[TD]83.2%[/TD]
[TD]N/A[/TD]
[TD]82.0%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]88.2%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]77.7%[/TD]
[TD]80.9%[/TD]
[TD]78.1%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]N/A[/TD]
[TD]73.7%[/TD]
[TD]N/A[/TD]
[TD]78.8%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]89.9%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]85.2%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]82.5%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]93.6%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]85.7%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]N/A[/TD]
[TD]81.1%[/TD]
[TD]91.1%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]83.6%[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Demand[/TD]
[TD="align: right"]35000[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]20000[/TD]
[TD="align: right"]150000[/TD]
[TD="align: right"]40000[/TD]
[TD="align: right"]30000[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]20000[/TD]
[TD="align: right"]10000[/TD]
[TD="align: right"]1000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]Machine daily output (goods&scraps)[/TD]
[TD="align: right"]1600[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Inside the table the % are the quality performance for each machine

I'm really stuck in here and will appriciate your help.

thank you in advance