Comparing Cells within a single column

[tek]

Hi Guys

I am totally new to programming in excel. I am trying to organise a spreadsheet to schedule machines.

In my spreadsheet, I have column A, to identify a machine (Eg. RDX). In column B I have the date range that the machine will run for (e.g 27/07/2011 - 01/08/2011)

What I am trying to achieve is, somehow for each row in column B to compare with all the other rows in column B and and compare with column A. So if there is a clash where two tasks have been scheduled for the same machine at the same dates, it should highlight in red and return a message.

I know this sounds extremely tedious. But it would make life easier as currently I am finding I spend 30mins to prepare a schedule only to find a clash later and then manually re-adjusting everything.

 

[Tigs81]

Hi Tek
Try this, I have gone a slightly different way

Column I

=CONCATENATE(O2," Tasks Assigned",IF(Q2>0,CONCATENATE(" - ",SUMPRODUCT(--(SIGN(((D2<=$D$2:$D$6)*(E2>=$D$2:$D$6))+((D2<=$E$2:$E$6)*(E2>=$E$2:$E$6))+((D2<=$D$2:$D$6)*(E2>=$E$2:$E$6)))*--(B2=$B$2:$B$6)))," Power Supply Double Booked"),""))

Column J

=CONCATENATE(IF(SUMPRODUCT(--(SIGN(((D2<=$D$2:$D$6)*(E2>=$D$2:$D$6))+((D2<=$E$2:$E$6)*(E2>=$E$2:$E$6))+((D2<=$D$2:$D$6)*(E2>=$E$2:$E$6))))*--(G2<>$G$2:$G$6)*--(A2=$A$2:$A$6))>0,CONCATENATE(" - ",SUMPRODUCT(--(SIGN(((D2<=$D$2:$D$6)*(E2>=$D$2:$D$6))+((D2<=$E$2:$E$6)*(E2>=$E$2:$E$6))+((D2<=$D$2:$D$6)*(E2>=$E$2:$E$6))))*--(G2<>$G$2:$G$6)*--(A2=$A$2:$A$6))," Temp Conflicts"),""),IF(SUMPRODUCT(--(SIGN(((D2<=$D$2:$D$6)*(E2>=$D$2:$D$6))+((D2<=$E$2:$E$6)*(E2>=$E$2:$E$6))+((D2<=$D$2:$D$6)*(E2>=$E$2:$E$6))))*--(H2<>$H$2:$H$6)*--(A2=$A$2:$A$6))>0,CONCATENATE(" - ",SUMPRODUCT(--(SIGN(((D2<=$D$2:$D$6)*(E2>=$D$2:$D$6))+((D2<=$E$2:$E$6)*(E2>=$E$2:$E$6))+((D2<=$D$2:$D$6)*(E2>=$E$2:$E$6))))*--(H2<>$H$2:$H$6)*--(A2=$A$2:$A$6))," Voltage Conflicts"),""))

HTML
Excel 2003<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Machine</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Power Supply</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Test Period</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">to</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">yo</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Standard/section</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Temp conditions</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid">Power conditions (Volts)</TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid"></TD><TD style="TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: black 1px solid"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">GAA</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">supply1</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">05/08/2011 - 09/09/2011</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">40760</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">40795</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">-30</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">24</TD><TD style="BORDER-LEFT: black 1px solid">3 Tasks Assigned</TD><TD>- 2 Temp Conflicts - 1 Voltage Conflicts</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">GPO</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">supply1</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">05/02/2011 - 07/02/2011</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">40579</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">40581</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">-40</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">24</TD><TD style="BORDER-LEFT: black 1px solid">1 Tasks Assigned - 2 Power Supply Double Booked</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">GBB</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">supply2</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">05/08/2011 - 09/09/2011</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">40760</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">40795</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">-40</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">12</TD><TD style="BORDER-LEFT: black 1px solid">1 Tasks Assigned</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">GAA</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">supply1</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">05/08/2011 - 09/09/2011</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">40760</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">40795</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">-40</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">24</TD><TD style="BORDER-LEFT: black 1px solid">3 Tasks Assigned</TD><TD>- 1 Temp Conflicts - 1 Voltage Conflicts</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">test</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">supply1</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">05/02/2011 - 07/02/2011</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">40579</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">40581</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">-40</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">24</TD><TD style="BORDER-LEFT: black 1px solid">1 Tasks Assigned - 2 Power Supply Double Booked</TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">GAA</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">supply1</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">05/08/2011 - 09/09/2011</TD><TD style="TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BORDER-RIGHT: black 1px solid">40760</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">40795</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: right; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid"></TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">-40</TD><TD style="BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center; BORDER-LEFT: black 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-TOP: black 1px solid; BORDER-RIGHT: black 1px solid">12</TD><TD style="BORDER-LEFT: black 1px solid">3 Tasks Assigned</TD><TD>- 1 Temp Conflicts - 2 Voltage Conflicts</TD></TR></TBODY></TABLE>

Sheet1​

Thanks
Tigs

 

[tek]

Hey Tigs.

Can you explain for your first code (for column i), what columns O and Q are for?

 

[Tigs81]

Sorry.
i had split it all up and forgot put it back together

here is column I again

=CONCATENATE(SUMPRODUCT(--(SIGN(((D2<=$D$2:$D$7)*(E2>=$D$2:$D$7))+((D2<=$E$2:$E$7)*(E2>=$E$2:$E$7))+((D2<=$D$2:$D$7)*(E2>=$E$2:$E$7)))*--(A2=$A$2:$A$7)))," Tasks Assigned",IF(SUMPRODUCT(--(SIGN(((D2<=$D$2:$D$7)*(E2>=$D$2:$D$7))+((D2<=$E$2:$E$7)*(E2>=$E$2:$E$7))+((D2<=$D$2:$D$7)*(E2>=$E$2:$E$7))))*--(A2<>$A$2:$A$7)*--(B2=$B$2:$B$7))>0,CONCATENATE(" - ",SUMPRODUCT(--(SIGN(((D2<=$D$2:$D$7)*(E2>=$D$2:$D$7))+((D2<=$E$2:$E$7)*(E2>=$E$2:$E$7))+((D2<=$D$2:$D$7)*(E2>=$E$2:$E$7)))*--(B2=$B$2:$B$7)))," Power Supply Double Booked"),""))

 

[tek]

Your code works good for the conditions I have stated man. Thanks for taking your time to write it out for me.

But both codes stop working once you delete a cell in column B and H. Sorry I should've specified this earlier but, in some cases I will just be runing the machine only and will not be using any power supply. So in cases like that, the cell for column B and H will be blank.

When mucking around with it, I saw the only way around to the problem was to write na in the cell for column B and write 0 in the cell for column H. Is there a way that the code for column 'i' and "j" can take blank cells (For columns B and H) into account?

 

[Tigs81]

No worries.
I am not sure, it seems to work fine for me when i make a cell blank.

 

[tek]

Can you make it so that it shows which power supply is double booked in column "I". I mean in the error message that returns you actually see which power supply is double booked. So instead of the 'Power Supply double booked" message, you see "<name of supply> double booked".

Same thing with column "j' instead of just temperature conflict and voltage conflict. Can you make it so that it says "<name of machine> temp conflict" and "<name of power supply> voltage conflict" ?

 

[tek]

Can you make it so that it shows which power supply is double booked in column "I". I mean in the error message that returns you actually see which power supply is double booked. So instead of the 'Power Supply double booked" message, you see "<name of="" supply=""> double booked".

Same thing with column "j' instead of just temperature conflict and voltage conflict. Can you make it so that it says "<name of="" machine=""> temp conflict" and "<name of="" power="" supply=""> voltage conflict" ?

Not sure why it got left out..

So instead of the "Power Supply double booked" message, you see "(Name of power supply) double booked"?

Same thing with column "j" as well. Instead of just temperature conflict and voltage conflict messgae. Can it say "(Name of Machine) - temp conflict" and "(Name of power supply) - voltage conflict" ?
</name></name></name>

 

[Tigs81]

Tek

You cant do it using the formula that I have created that I know of.
However you should be able to do it using an Array Formula, or VBA.

Thanks
Tigs