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.

 

[bensonsearch]

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.

to be honest, me personally would filter the data by machine and check the dates are correct. however this can be done via code as well.

when you say new to programming how new?

is the data large? do you happen to be able to show example of a sheet?

 

[Tigs81]

Hi
I dont think that you need to use programming to get the result that you want.

If in Column A you have the computer name
If in Column B you have the text format of 27/07/2011 - 01/08/2011

then
In Column C extract the dateserial number of the first date

=DATEVALUE(LEFT(B1,10))

In Column D extract the date serial number of the second date

=DATEVALUE(RIGHT(B1,10))

In Column E do the comparsion using the sumproduct formula

=IF(SUMPRODUCT(--(--(((C1<=$C$1:$C$5)*--(D1>=$C$1:$C$5))+--((C1<=$D$1:$D$5)*--(D1>=$D$1:$D$5))+--((C1<=$C$1:$C$5)*--(D1>=$D$1:$D$5)))*--(A1=$A$1:$A$5)))>3,"DOUBLE BOOKED","")

I am sure that the sumproduct formula that i have used can be made better.

then just fill the formula's in column C,D and E down the list.

Thanks
Tigs

 

[Tigs81]

this one might cover all the options in column E instead of the previous one used.
<CODE>
=IF(SUMPRODUCT(--(($C$1:$C$50>=C1)*--($C$1:$C$50<=D1)+--($D$1:$D$50>=C1)*--($D$1:$D$50<=D1)+--($C$1:$C$50>=C1)*--($D$1:$D$50<=D1)+--($C$1:$C$50<=C1)*--($D$1:$D$50>=D1))*--(A1=$A$1:$A$50))>4,"DOUBLE BOOKED","")</CODE>

 

[tek]

to be honest, me personally would filter the data by machine and check the dates are correct. however this can be done via code as well.

when you say new to programming how new?

is the data large? do you happen to be able to show example of a sheet?

Hey Bensonsearch
I will post a screenshot of a spreadsheet later. But I could schedule upto 30-40 tasks at a time with different machines in the one spreadsheet.
Also I have done simple c-programming in the past. But I havent touched it for a while.


Tigs81 your a genius . It works beautifully.

Can I trouble you again though?
I want to add a column E and F. Column E1 will be titled "POWER ON" and every cell below column E1 will either have a "Yes" or a "No". Same thing as before I want the date ranges compared with the machine code and in addition this time with the "yes" or "no" (column E) as well. The result of this comparison will be in Column F. So column F will give me the Double booked message.

Thanks again Mate.

 

[Peter_SSs]

In relation to the original problem & solution, I have some comments & suggestions. These things may not be possible with your data, but the suggestion may fail if ..

- there are any blank rows in the data.
- it is possible that the date range cell does not always have full 10 character dates (eg 1/7/2011 - 3/7/2011 or 01/08/11 - 03/08/11)

If I have understood correctly the checking formula can also be simplified considerably. Try the suggestion below, choosing the column E or F formula depending on your Excel version. The column F formula will work in all versions. The column E one is a little more efficient but only works in recent versions.

All formulas copied down.

Excel Workbook
	A	B	C	D	E	F
1	Machine	Period	Start	End	2007+	2003-
2	RDX	27/7/11 - 1/8/11	27/07/11	1/08/11	DOUBLE BOOKED	DOUBLE BOOKED
3	RDX	28/7/11 - 5/8/11	28/07/11	5/08/11	DOUBLE BOOKED	DOUBLE BOOKED
4	ABC	28/7/11 - 5/8/11	28/07/11	5/08/11
5	XYZ
6	DEF	10/7/11 - 26/7/11	10/07/11	26/07/11	DOUBLE BOOKED	DOUBLE BOOKED
7
8	DEF	5/8/11 - 7/8/11	5/08/11	7/08/11
9	RDX	28/7/11 - 5/8/11	28/07/11	5/08/11	DOUBLE BOOKED	DOUBLE BOOKED
10	ABC	6/8/11-10/8/11	6/08/11	10/08/11
11	DEF	1/07/2011 - 28/7/11	1/07/11	28/07/11	DOUBLE BOOKED	DOUBLE BOOKED
12	RDX	01/09/2011 - 03/09/2011	1/09/11	3/09/11
13
Check Conflict

 

[tek]

Sweet. Figured it out how to do it myself. But I have a thirst to expand this spreadsheet now.

Something like this.

Let me explain a bit more. Ok so in addition to the date range conditions (Column D and E) like before, I want this spreadsheet to do the following.

If a machine is scheduled to run two tasks at the same temperature, then I only want the double booking message if it has to use the same power supply twice (Like in Cell 2 and 4). Some message like "Supply double booked"

But if the same machine is scheduled to run two tasks at different temperatues (Like in Cell 4 and 5), then I want some message like "Machine double booked".

This way it makes it easier for me, to quickly identify which cells are causing the clash, and the reason for it.

Hope I have been clear in what I am trying to achieve.

P.S: I love the immediate update that excel does. That truly surprised me.

 

[Peter_SSs]

Any chance you could investigate one of the three screen shot methods shown in my signature block? That way you can post your small sample data directly in your thread (like I did above) and potential helpers can copy your sample data (and any formulas if you use one of the first two methods) to their own sheet to test. Most of us don't much like manually typing out test data.

 

[tek]

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 105px"><COL style="WIDTH: 116px"><COL style="WIDTH: 162px"><COL style="WIDTH: 137px"><COL style="WIDTH: 181px"><COL style="WIDTH: 123px"><COL style="WIDTH: 84px"><COL style="WIDTH: 96px"><COL style="WIDTH: 248px"></COLGROUP><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></TR><TR style="HEIGHT: 18px"><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 cond</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Power cond</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Double Booking Pre-caution</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: center">GXX</TD><TD style="TEXT-ALIGN: center">supply1</TD><TD style="TEXT-ALIGN: center">28/07/2011 - 02/08/2011</TD><TD style="TEXT-ALIGN: center">40752</TD><TD style="TEXT-ALIGN: center">40757</TD><TD style="TEXT-ALIGN: center">ISO1000/1.1</TD><TD style="TEXT-ALIGN: center">85°C</TD><TD style="TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: center">Should say "supply 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">GXX</TD><TD></TD><TD style="TEXT-ALIGN: center">28/07/2011 - 02/08/2011</TD><TD style="TEXT-ALIGN: center">40752</TD><TD style="TEXT-ALIGN: center">40757</TD><TD style="TEXT-ALIGN: center">IEC1500/6.8</TD><TD style="TEXT-ALIGN: center">85°C</TD><TD style="TEXT-ALIGN: center">OFF</TD><TD></TD></TR><TR style="HEIGHT: 55px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: center">GXX</TD><TD style="TEXT-ALIGN: center">supply1</TD><TD style="TEXT-ALIGN: center">28/07/2011 - 02/08/2011</TD><TD style="TEXT-ALIGN: center">40752</TD><TD style="TEXT-ALIGN: center">40757</TD><TD style="TEXT-ALIGN: center">ISO1000/1.2</TD><TD style="TEXT-ALIGN: center">85°C</TD><TD style="TEXT-ALIGN: center">24</TD><TD style="TEXT-ALIGN: center">Should say "Machine double booked" and "supply double booked" in this cell </TD></TR><TR style="HEIGHT: 36px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: center">GXX</TD><TD style="TEXT-ALIGN: center">supply2</TD><TD style="TEXT-ALIGN: center">28/07/2011 - 02/08/2011</TD><TD style="TEXT-ALIGN: center">40752</TD><TD style="TEXT-ALIGN: center">40757</TD><TD style="TEXT-ALIGN: center">ISO1000/1.1</TD><TD style="TEXT-ALIGN: center">75°C</TD><TD style="TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: center">Should say "Machine double booked" in this cell</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>D3</TD><TD>=DATEVALUE(LEFT(C3,10))</TD></TR><TR><TD>E3</TD><TD>=DATEVALUE(RIGHT(C3,10))</TD></TR><TR><TD>D4</TD><TD>=DATEVALUE(LEFT(C4,10))</TD></TR><TR><TD>E4</TD><TD>=DATEVALUE(RIGHT(C4,10))</TD></TR><TR><TD>D5</TD><TD>=DATEVALUE(LEFT(C5,10))</TD></TR><TR><TD>E5</TD><TD>=DATEVALUE(RIGHT(C5,10))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Hope I have done this right. Also don't worry I won't have spaces between my entries. But the <10 character error is a worry nonetheless.

 

[Peter_SSs]

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?