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:

ROOM	COURSE	START DATE	END DATE
room1	maths	16/02/2014	17/02/2014
room1	english	18/02/2014	20/02/2014
room1	chemistry	22/02/2014	24/02/2014
room1	physics	25/02/2014	27/02/2014
room1	football	18/02/2014	20/02/2014

<tbody>
</tbody>

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