Identifying room clashes with dates

[MrFahad123]

Hi Guys,

I wanted to ask for some advice on finding Room clashes (e.g two classes happening in the same room on the same date. My aim is to apply whatever method is advised to a large amount of data (1000 classes in 20 different rooms).

eg. below:
[TABLE="width: 10"]
<tbody>[TR]
[TD]ROOM[/TD]
[TD]COURSE[/TD]
[TD]START DATE[/TD]
[TD]END DATE[/TD]
[/TR]
[TR]
[TD]room1[/TD]
[TD]maths[/TD]
[TD]16/02/2014[/TD]
[TD]17/02/2014[/TD]
[/TR]
[TR]
[TD]room1[/TD]
[TD]english[/TD]
[TD]18/02/2014[/TD]
[TD]20/02/2014[/TD]
[/TR]
[TR]
[TD]room1[/TD]
[TD]chemistry[/TD]
[TD]22/02/2014[/TD]
[TD]24/02/2014[/TD]
[/TR]
[TR]
[TD]room1[/TD]
[TD]physics[/TD]
[TD]25/02/2014[/TD]
[TD]27/02/2014[/TD]
[/TR]
[TR]
[TD]room1[/TD]
[TD]football[/TD]
[TD]18/02/2014[/TD]
[TD]20/02/2014[/TD]
[/TR]
</tbody>[/TABLE]

as you can see football clashes with english.



Thanks for your time.

 

[mfexcel]

Are you going to assign available classroom to different classes? or the input is done already (as shown) and you need to identify if there is any crash?

 

[MrFahad123]

hi mfexcel,

the input is done already, so I just need to identify the clashes. what do you recommend?

 

[Jubjab]

try in E2, fill down

=SUMPRODUCT(--($A$2:$A$6=A2),--($C$2:$C$6<=D2),--($D$2:$D$6>=C2))

any result over 1 means a clash while 1 means no clash

 

[mfexcel]

just in case you are using Excel 2007 or later, you may also consider
In E2
=COUNTIFS($A$2:$A$6,A2,$C$2:$C$6,"<="&D2,$D$2:$D$6,">="&C2)

to further elaborate jubjab's point, any result over 1 means a clash; the number actually reflects how many crashes encountered. 2 means 2 classes crashes for the same room for a particular date; 3 means 3 classes... etc.

 

[MrFahad123]

Hi mfexcel and jubjab,

Thank you so much, this information is helpful and works perfectly. What adjustments to the formula do I make if there are more than one room involed in the colum? Do I just replace criteria 1 with the whole column? (i.e; A:A instead of A2) and (i.e; C:C instead of C2)

again thanks for your prom

 

[mfexcel]

You are welcome. Glad that it helps.

To extend the list, just change $A$2:$A$6 (and the ranges) to $A$2:$A$XXX where XXX is the last row of your table

Hi mfexcel and jubjab,

Thank you so much, this information is helpful and works perfectly. What adjustments to the formula do I make if there are more than one room involed in the colum? Do I just replace criteria 1 with the whole column? (i.e; A:A instead of A2) and (i.e; C:C instead of C2)

again thanks for your prom