University schedule - check for duplicates and/or overlap

[anghicole]

Hi,

I have compiled schedules for severeal classes/groups and I would like to check for duplicates based on information from multiple cells. What I would like to check is if the "date" (column F), "time" (column I), and "Person" (column J) is the same or overlap. E.g. the person is scheduled for one group from 8-12 but also with another group from 10-14 (which obviously won't be ideal). I would also like to do the same control for date, time and room, so that two groups doesn't occupy the same room at the same time, or their classes overlap at the same room.

With som help I was able to concatentate and match text strings, wich highlights the row if the above information is excatly the same, but not if the time overlaps.

Any help would be appriciated! Feel free to add any helper columns, spilt the time column in start/end time or whatever is needed. I am pretty familiar with hos to build formulas, but I just can't figure out how to solve this. So if anyone is able to point me in the right direction or present any working formula I would be very happy. If VBA/macro is needed thats okey, I am familiar with basic VBA.
Workbook attached.

[oeldere]

To get you started.

I added column J and K to split the begin and endtime.

I used vlookup for that.

The vlookup table is on the sheet table.

after that I made a pivot table to sort the data.

after that I made a helpcolumn in sheet Oeldere in column M.

In column N there is a countif formula to count if there is a duplication.

After that you can filter on all values > 1.

There you will see all duplicated data (sorted on date).

I don't have a solution yet to see if there is a overlap in rooms.

See the attached file.

Edit => use the new added file (oeldere2) instead.

Then the names of the rooms are below eachother.

[Richard Buttrey]

Are you able to identify any duplicates since I can't see any?

Since the times are dependent on the Tid field this is simplified since presumably we don't need to worry about the absolute time range.

I concatenated the Person, date and Tid value in a helper column and then used a simple COUNTIF on each row to count how many times the helper column value appeared in the whole of the helper column - and couldn't see any.

[oeldere]

@Richard Buttrey

28-8-2017 Borre Idrettspark 10:15:00 11:45:00 Fredrik Lie Haugen
28-8-2017 Borre Idrettspark 10:15:00 11:45:00 Steinar Øvreås

both rooms are duplicated (see my sheet in #2)

[Richard Buttrey]

@oeldere.

Quite correct. I'd missed the bit about the room.

[anghicole]

Thank you very much for your replies.
Your solution is quite good oeldere. Any idea on how to detect the overlapping sessions? Thats the really anyoing problem.

[oeldere]

No, not have found a solution for that.

But since the data are sorted by date and then by room, it looks to me you can easily check that manualy.

As start for an solution, you can add which times (tid) are overlapping.

You can add the tid (code) in e.g. column E on the sheet table.

Maybe we can work with that value (tid)