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.

 

[tek]

That's much better!

Not really a problem here because you don't have many formulas but you can use the ‘Analyse range (Forum)’ field near the top left of the Excel jeanie screen to restrict the number of formulas generated, as I did in my earlier post. In that field, you can use Ctrl+Click/Drag to select multiple disjoint ranges if required. There is generally no need to display multiple formulas that are basically the same, it just fills up the board.

I don't really understand you new requirement.

Do the dates come into this at all?

Why isn't the "Machine Double Booked" on every row of this sample since it is the same machine and same dates?

Basically when multiple tasks require the same temperature and same machine. I can cram all the tasks in. So you can see this from Cell 2, 3 and 4. Where the machine GXX is being run at 85C for three different tasks.

BUT!!

If they all require a power supply. Then the power supply unit and voltage applied becomes the limiting factor. You can see this in Cell 2 and Cell 4. "Supply 1" is accidentally inputed twice to provide 12V and 24V, which cannot be done.

So I would want to see a message like "Supply double booked" in Cell 2 and 4.

On the other hand. If the same machine has been inputed in Column A, but at different temperatures, as is the case in Cell 4 and 5. Then that should give me a message as well, like "machine double booked" in Cell 4 and 5.

You can see the machine GXX, has two different temperature values (85C in G4 and 75C in G5).

 

[tek]

Ok I tried some stuff with tigs81's code.

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD></TR><TR style="HEIGHT: 55px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Machine</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Power Supply</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Test Period</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">to</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">yo</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Standard/section</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Temp conditions
(°C)
</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Power conditions (Volts)</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Power Supply Double Booked</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Machine Double Booked</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center">GAA</TD><TD style="TEXT-ALIGN: center">supply1</TD><TD style="TEXT-ALIGN: center">05/08/2011 - 09/09/2011</TD><TD style="TEXT-ALIGN: right">40760</TD><TD style="TEXT-ALIGN: right">40795</TD><TD></TD><TD style="TEXT-ALIGN: center">-30</TD><TD style="TEXT-ALIGN: center">12</TD><TD></TD><TD>DOUBLE BOOKED</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center">GPO</TD><TD style="TEXT-ALIGN: center">sgi</TD><TD style="TEXT-ALIGN: center">05/02/2011 - 07/02/2011</TD><TD style="TEXT-ALIGN: right">40579</TD><TD style="TEXT-ALIGN: right">40581</TD><TD></TD><TD style="TEXT-ALIGN: center">-40</TD><TD style="TEXT-ALIGN: center">24</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center">GBB</TD><TD style="TEXT-ALIGN: center">supply2</TD><TD style="TEXT-ALIGN: center">05/08/2011 - 09/09/2011</TD><TD style="TEXT-ALIGN: right">40760</TD><TD style="TEXT-ALIGN: right">40795</TD><TD></TD><TD style="TEXT-ALIGN: center">-40</TD><TD style="TEXT-ALIGN: center">12</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center">GAA</TD><TD style="TEXT-ALIGN: center">supply1</TD><TD style="TEXT-ALIGN: center">05/08/2011 - 09/09/2011</TD><TD style="TEXT-ALIGN: right">40760</TD><TD style="TEXT-ALIGN: right">40795</TD><TD></TD><TD style="TEXT-ALIGN: center">-40</TD><TD style="TEXT-ALIGN: center">24</TD><TD>SUPPLY BOOKED</TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: center">GAA</TD><TD style="TEXT-ALIGN: center">supply1</TD><TD style="TEXT-ALIGN: center">05/08/2011 - 09/09/2011</TD><TD style="TEXT-ALIGN: right">40760</TD><TD style="TEXT-ALIGN: right">40795</TD><TD></TD><TD style="TEXT-ALIGN: center">-40</TD><TD style="TEXT-ALIGN: center">12</TD><TD></TD><TD></TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D2</TD><TD>=DATEVALUE(LEFT(C2,10))</TD></TR><TR><TD>E2</TD><TD>=DATEVALUE(RIGHT(C2,10))</TD></TR><TR><TD>I2</TD><TD>=IF(SUMPRODUCT(--(($D$2:$D$50>=D2)*--($D$2:$D$50<=E2)+--($E$2:$E$50>=D2)*--($E$2:$E$50<=E2)+--($D$2:$D$50>=D2)*--($E$2:$E$50<=E2)+--($D$2:$D$50<=D2)*--($E$2:$E$50>=E2))*--(B2=$B$2:$B$50)*--(H2<>$H$2:$H$50))>5,"SUPPLY BOOKED","")</TD></TR><TR><TD>J2</TD><TD>=IF(SUMPRODUCT(--(($D$2:$D$50>=D2)*--($D$2:$D$50<=E2)+--($E$2:$E$50>=D2)*--($E$2:$E$50<=E2)+--($D$2:$D$50>=D2)*--($E$2:$E$50<=E2)+--($D$2:$D$50<=D2)*--($E$2:$E$50>=E2))*--(A2=$A$2:$A$50)*--(G2<>$G$2:$G$50))>4,"DOUBLE BOOKED","")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


My question now is, can you tell me how to modify the If statement so that in Column I and J, it tells me in the message which machine or power supply has been double booked.

Cheers!

 

[tek]

Nvm, back to the programming board. Somethings flawed, it wont work now.


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD></TR><TR style="HEIGHT: 55px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Machine</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Power Supply</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Test Period</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">to</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">yo</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Standard/section</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Temp conditions
(°C)
</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Power conditions (Volts)</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Power Supply Double Booked</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Machine Double Booked</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center">GAA</TD><TD style="TEXT-ALIGN: center">supply1</TD><TD style="TEXT-ALIGN: center">05/08/2011 - 09/09/2011</TD><TD style="TEXT-ALIGN: right">40760</TD><TD style="TEXT-ALIGN: right">40795</TD><TD></TD><TD style="TEXT-ALIGN: center">-30</TD><TD style="TEXT-ALIGN: center">12</TD><TD></TD><TD>DOUBLE BOOKED</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: center">GPO</TD><TD style="TEXT-ALIGN: center">sgi</TD><TD style="TEXT-ALIGN: center">05/02/2011 - 07/02/2011</TD><TD style="TEXT-ALIGN: right">40579</TD><TD style="TEXT-ALIGN: right">40581</TD><TD></TD><TD style="TEXT-ALIGN: center">-40</TD><TD style="TEXT-ALIGN: center">24</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center">GBB</TD><TD style="TEXT-ALIGN: center">supply2</TD><TD style="TEXT-ALIGN: center">05/08/2011 - 09/09/2011</TD><TD style="TEXT-ALIGN: right">40760</TD><TD style="TEXT-ALIGN: right">40795</TD><TD></TD><TD style="TEXT-ALIGN: center">-40</TD><TD style="TEXT-ALIGN: center">12</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center">GAA</TD><TD style="TEXT-ALIGN: center">sgi</TD><TD style="TEXT-ALIGN: center">05/08/2011 - 09/09/2011</TD><TD style="TEXT-ALIGN: right">40760</TD><TD style="TEXT-ALIGN: right">40795</TD><TD></TD><TD style="TEXT-ALIGN: center">-40</TD><TD style="TEXT-ALIGN: center">20</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: center">GAA</TD><TD style="TEXT-ALIGN: center">supply1</TD><TD style="TEXT-ALIGN: center">05/08/2011 - 09/09/2011</TD><TD style="TEXT-ALIGN: right">40760</TD><TD style="TEXT-ALIGN: right">40795</TD><TD></TD><TD style="TEXT-ALIGN: center">-40</TD><TD style="TEXT-ALIGN: center">12</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: center">GTST</TD><TD style="TEXT-ALIGN: center">supply3</TD><TD style="TEXT-ALIGN: center">01/08/2011 - 03/08/2011</TD><TD style="TEXT-ALIGN: right">40756</TD><TD style="TEXT-ALIGN: right">40758</TD><TD></TD><TD style="TEXT-ALIGN: center">75</TD><TD style="TEXT-ALIGN: center">30</TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: center">GTST</TD><TD style="TEXT-ALIGN: center">supply3</TD><TD style="TEXT-ALIGN: center">01/08/2011 - 03/08/2011</TD><TD style="TEXT-ALIGN: right">40756</TD><TD style="TEXT-ALIGN: right">40758</TD><TD></TD><TD style="TEXT-ALIGN: center">85</TD><TD style="TEXT-ALIGN: center">20</TD><TD></TD><TD></TD></TR></TBODY></TABLE>

<TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR><TD>D8</TD><TD>=DATEVALUE(LEFT(C8,10))</TD></TR><TR><TD>E8</TD><TD>=DATEVALUE(RIGHT(C8,10))</TD></TR><TR><TD>I8</TD><TD>=IF(SUMPRODUCT(--(($D$2:$D$50>=D8)*--($D$2:$D$50<=E8)+--($E$2:$E$50>=D8)*--($E$2:$E$50<=E8)+--($D$2:$D$50>=D8)*--($E$2:$E$50<=E8)+--($D$2:$D$50<=D8)*--($E$2:$E$50>=E8))*--(B8=$B$2:$B$50)*--(H8<>$H$2:$H$50))>4,"SUPPLY BOOKED","")</TD></TR><TR><TD>J8</TD><TD>=IF(SUMPRODUCT(--(($D$2:$D$50>=D8)*--($D$2:$D$50<=E8)+--($E$2:$E$50>=D8)*--($E$2:$E$50<=E8)+--($D$2:$D$50>=D8)*--($E$2:$E$50<=E8)+--($D$2:$D$50<=D8)*--($E$2:$E$50>=E8))*--(A8=$A$2:$A$50)*--(G8<>$G$2:$G$50))>4,"DOUBLE BOOKED","")</TD></TR></TBODY></TABLE>


Both Cell 7 and 8 should say double booked in columns I and J.

 

[Peter_SSs]

Basically when multiple tasks require the same temperature and same machine. I can cram all the tasks in. So you can see this from Cell 2, 3 and 4. Where the machine GXX is being run at 85C for three different tasks.

BUT!!

If they all require a power supply. Then the power supply unit and voltage applied becomes the limiting factor. You can see this in Cell 2 and Cell 4. "Supply 1" is accidentally inputed twice to provide 12V and 24V, which cannot be done.

So I would want to see a message like "Supply double booked" in Cell 2 and 4.

On the other hand. If the same machine has been inputed in Column A, but at different temperatures, as is the case in Cell 4 and 5. Then that should give me a message as well, like "machine double booked" in Cell 4 and 5.

You can see the machine GXX, has two different temperature values (85C in G4 and 75C in G5).

I asked about dates and you still haven't mentioned them in your reply. Surely you are not saying that if all the date ranges in that sample data were completely separate that machines and/or power supplies would still be double booked?



You also say that rows 4 and 5 should say Machine double booked because you have the same machine and different temperatures. That's also true for rows 3 and 5 and true for rows 2 and 5 so if row 4 shows Machine double booked why don't rows 3 and 2 also say that?

 

[Tigs81]

Tek

I agree with Peter_SSs. I dont understand your parameter relationships.
With the new changes of adding in the power supply. Does the power supply have any relationship to the machine name and/or date range?

maybe explaining each of the two cells individually what they should acheive.
eg

Machine Double
IF
The Machine' Test Period intercets another test period of a machine with the same machine name
THEN
"DOUBLE BOOKED"

--I think that you now have added additional parameters to this though. Please add.

Power Supply Double
Not Sure what the conditions are

IF
The Power Supply Test Period intercets another test period of a power supply with the same name
AND
??
THEN
"SUPPLY BOOKED"

Thanks
Tigs

 

[tek]

The dates directly affect both the machines and the power supplies. I only have three power supplies on me and limited to perform three powered tasks at a time.

Scheduling multiple tasks using the same machine and power supply, in the same time frame is acceptable. As long as each task has the same temperature settings (For that particular machine – Eg. GTST) and same supply voltage value (For that particular power supply – Eg. Supply3).
Otherwise, a clash will occur due to the following conditions:

First Condition: The same machine has been booked for two or more tasks in the same time period, but it will be at different temperature settings. So this is a clash between two or more tasks due to the machine.

Second Condition: The same power supply (Eg. Supply3) has been booked for two or more tasks in the same time period. The only exception to this condition is that these multiple tasks require the same machine, same temperature setting and the same voltage value. In all other instances this is a clash between two or more tasks due to the power supply. So if the same power supply is required in the same time period for two tasks using different machines, then it is a clash.

Please have a look at Rows 7 and 8 specifically as an example.

So looking at "supply3" in cell B7 and H7, you see that I need that power supply between 01/08/2011 to 03/08/2011 to run a task in the GTST machine (See Cell A7). In this task that power supply has to provide 30V and the machine (GTST) will be running at 75C (Cell G7).

BUT!!

I have scheduled that same power supply and machine during the same dates, for another task (See cell B8 and H8). As a result there is a clash between these two tests because of both the machine (GTST) and the power supply (supply3). The power supply has to provide 20V in this task and the machine has to run at 85C. So in this instance I would have to modify the dates for one of these tasks so that it runs after the other, to clear this clash.

In the second instance, if the machine had to have run at 75C and the power supply would’ve had to provide 30V, in the same time frame as row 7. This would have been fine and it would not have been a clash.


Thanks for your input guys, appreciate this

 

[Tigs81]

Hi Tek
I assume that a power supply can be in a different machine and therefore need to check if the power supply and/or computer are being used at the same time with another computer and/or power supply?
or will the power supply be the same computer all the time.

Thanks
Tigs

 

[Tigs81]

How about this
Column I

=IF(SUMPRODUCT(--(--(((D3<=$D$3:$D$7)*--(E3>=$D$3:$D$7))+--((D3<=$E$3:$E$7)*--(E3>=$E$3:$E$7))+--((D3<=$D$3:$D$7)*--(E3>=$E$3:$E$7)))*--(B3=$B$3:$B$7)*--(G3<>$G$3:$G$7)*--(H3<>$H$3:$H$7)))>=3,"SUPPLY BOOKED","")

Column J

=IF(SUMPRODUCT(--(--(((D3<=$D$3:$D$7)*--(E3>=$D$3:$D$7))+--((D3<=$E$3:$E$7)*--(E3>=$E$3:$E$7))+--((D3<=$D$3:$D$7)*--(E3>=$E$3:$E$7)))*--(A3=$A$3:$A$7)))>3,"DOUBLE BOOKED","")

 

[tek]

Hi Tek
I assume that a power supply can be in a different machine and therefore need to check if the power supply and/or computer are being used at the same time with another computer and/or power supply?
or will the power supply be the same computer all the time.

Thanks
Tigs

Hey Tigs

That's correct, the power supplies can be used for any machines. So a check is required to see if the same power supply (eg. supply3) is being used at the same time for two different machine, which isn't possible. The only exception to multiple tasks requiring that power supply is, they must require the same machine with the same temperature setting and same voltage setting.


P.S: Am I glad for you guys telling me about that html stuff. I am at home now and couldn't be bothered typing all that stuff again.


Edit: Sorry, but why in your code you start from the third row instead of the second? And the 3 in the end was a complete random number in my code. I just typed one number at a time until the spreadsheet did what I sort of wanted it to do.

 

[tek]

Sorry Im at home at the moment. So i'll just have to take a screenshot. I'll install that html add-on first thing tomorrow morning.

ok there is a problem with that spreadsheet.
While it is fine and pin-points that two tasks requiring the same machine and same power supply, clash because the voltage value is different (Rows 3 and 4).
The formula should also pick up that the task scheduled in row 5 is also clashing with the tasks from rows 3 and 4, because the same power supply is required for two different machines in the same time period.

I tried modifying your code a bit. But its not working the way i want it to.

For Column i

=IF(SUMPRODUCT(--(--(((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)*--(G2<>$G$2:$G$7)*--(H2<>$H$2:$H$7)*--(A2=$A$2:$A$7))+--((A2<>$A$2:$A$7)*--(B2=$B$2:$B$7))))>=3,"SUPPLY    BOOKED","")