venue arrangement: room, day, time clash check

[111111]

Hi
I need to arrange classes in different rooms and days at different times, I need to know if any two rows that conflict because of meeting place,day or time.

eg:1st and 2nd row have conflict.
room# day time
room1 M 1:00-2:00 conflict
room1 M 1:30-2:30 conflict
room2 F 1:00-2:00 ok
room3 W 1:00-2:00 ok

much appreciated for your help!

leo

[JeteMc]

Is that the definite format for your data? Could room#'s just be numbers? Could days be actual dates formatted to just show the name of the day? Could start and end times be in different columns? If so then the following formula will work:

Please Login or Register  to view this content.

Here is a file using your data with the changes and formula applied: Venue Arrangement.xlsx
Let me know if you have any questions.

[111111]

Thx JeteMc for your super speed!!
It works perfect!!

[JeteMc]

You're Welcome and thank you for the feedback. If you haven't already, please take a moment to mark the thread 'Solved' using the thread tools link above your first post. I hope that you have a good day.

[111111]

Hi JeteMc
Thx for reminding me, "Solved" marked.
Do you mind telling me how the SUMPRODUCT formula work?
or where can I find the explanation?
many thx
leo

[JeteMc]

The sumproduct works by finding occurrences where the room numbers in column A equal the specific room number in A2, occurrences where the days in column B equal the specific day in B2, occurrences where the times in column D are later than the specific time in C2 and occurrences where the times in column C are earlier than the specific time in D2. If you select one of the cells in column E, say E2, and run the evaluate formula from the data tab, you'll see that it multiplies trues and falses such that true * true = 1 and all other combinations equal zero.
Let me know if you have any questions.

[111111]

It's clear, thank you very much!!

[JeteMc]

You're Welcome and thank you for the feedback. I hope that you have a good day.