Multiple Condition formula or Macro or VBA in excel

[mmol_75]

Hello,
I need help with a multiple condition macro or formula or VBA solution in excel.

I have a spreadsheet where im tracking a production schedule. I would like to figure out how i can calculate the clean-up time based on the type of Pump we use, and based on other conditions:

- If the product has the same Family and Color, the Default is 20 min. (this would inferr that the pump would not need to be cleaned)
- If the product is not the same Family and/or Color, then the pump would have to be cleaned based on the conditions below

[TABLE="width: 359"]
<TBODY>[TR]
[TD]Job #</SPAN>[/TD]
[TD]Current Job</SPAN>[/TD]
[TD]Next Job</SPAN>[/TD]
[TD]Clean up time</SPAN>[/TD]
[/TR]
[TR]
[TD]1</SPAN>[/TD]
[TD]Pump A</SPAN>[/TD]
[TD]Pump A</SPAN>[/TD]
[TD]6 hr</SPAN>[/TD]
[/TR]
[TR]
[TD]2</SPAN>[/TD]
[TD]Pump B</SPAN>[/TD]
[TD]Pump B</SPAN>[/TD]
[TD]5 hr</SPAN>[/TD]
[/TR]
[TR]
[TD]3</SPAN>[/TD]
[TD]Pump A</SPAN>[/TD]
[TD]Pump B</SPAN>[/TD]
[TD]8 hr</SPAN>[/TD]
[/TR]
[TR]
[TD]4</SPAN>[/TD]
[TD]Pump B</SPAN>[/TD]
[TD]Pump A</SPAN>[/TD]
[TD]8 hr</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL></COLGROUP>[/TABLE]


The spreadsheet would be formatted in this fashon.
[TABLE="width: 271"]
<TBODY>[TR]
[TD]Job #</SPAN>[/TD]
[TD]Family</SPAN>[/TD]
[TD]Part B Color</SPAN>[/TD]
[TD]Pump Type</SPAN>[/TD]
[/TR]
[TR]
[TD]1</SPAN>[/TD]
[TD]9689</SPAN>[/TD]
[TD]Neutral</SPAN>[/TD]
[TD]Cherry</SPAN>[/TD]
[/TR]
[TR]
[TD]2</SPAN>[/TD]
[TD]9681</SPAN>[/TD]
[TD]Orange</SPAN>[/TD]
[TD]Cherry</SPAN>[/TD]
[/TR]
[TR]
[TD]3</SPAN>[/TD]
[TD]9658</SPAN>[/TD]
[TD]Neutral</SPAN>[/TD]
[TD]Cherry</SPAN>[/TD]
[/TR]
[TR]
[TD]4</SPAN>[/TD]
[TD]9845</SPAN>[/TD]
[TD]Black</SPAN>[/TD]
[TD]Cherry</SPAN>[/TD]
[/TR]
[TR]
[TD]5</SPAN>[/TD]
[TD]9696</SPAN>[/TD]
[TD]Neutral</SPAN>[/TD]
[TD]Cherry</SPAN>[/TD]
[/TR]
[TR]
[TD]6</SPAN>[/TD]
[TD]9658</SPAN>[/TD]
[TD]Neutral</SPAN>[/TD]
[TD]Cherry</SPAN>[/TD]
[/TR]
[TR]
[TD]7</SPAN>[/TD]
[TD]9628</SPAN>[/TD]
[TD]Neutral</SPAN>[/TD]
[TD]Mahr</SPAN>[/TD]
[/TR]
[TR]
[TD]8</SPAN>[/TD]
[TD]9696</SPAN>[/TD]
[TD]Green</SPAN>[/TD]
[TD]Mahr</SPAN>[/TD]
[/TR]
[TR]
[TD]9</SPAN>[/TD]
[TD]9628</SPAN>[/TD]
[TD]Neutral</SPAN>[/TD]
[TD]Mahr</SPAN>[/TD]
[/TR]
[TR]
[TD]10</SPAN>[/TD]
[TD]9628</SPAN>[/TD]
[TD]Neutral</SPAN>[/TD]
[TD]Mahr</SPAN>[/TD]
[/TR]
[TR]
[TD]11</SPAN>[/TD]
[TD]9628</SPAN>[/TD]
[TD]Neutral</SPAN>[/TD]
[TD]Mahr</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL span=3><COL></COLGROUP>[/TABLE]


Any help would be greatly appreciated....
[TABLE="width: 359"]
<TBODY>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL></COLGROUP>[/TABLE]

[TABLE="width: 295"]
<TBODY>[TR]
[TD][TABLE="width: 359"]
<TBODY>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]</SPAN>[/TD]
[TD]</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL></COLGROUP>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 319"]
<TBODY>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL></COLGROUP>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]

 

[patel45]

I did not understand anything

 

[HaHoBe]

Hi, mmol_75,

if possible you should try and swap jobs #7 and #8 as well as move #6 to #4 which would save 3 complete cleanings. Could you supply any further information where you would like the times to be listed and if maybe the list could be altered (right now it would be a total cleaning for job 1 to 8 as well as 11 and only small cleaning at #9 and 10).

Ciao,
Holger