Unique Values from 2 Columns of a table to an array

Cross Posted on Chandoo Forums as well

Hi All,

One of my staff is responsible for maintaining calendars for doctors in excel.
The data is in a table with Doctor Name, Date, Site, Start Time, End time.


Sample spreadsheet attached. Please note its not a range but table.

I need help with achieving 2 things:

1. Pick up unique values from Doctor and Site Column into a 2 dimensional array.
From the attached example, the array should have the following values:

Doc 1 Site 1
Doc 1 Site 2
Doc 2 Site 1
Doc 2 Site 2
Doc 2 Site 3
Doc 2 Site 4
Doc 3 Site 1
Doc 3 Site 2
Doc 3 Site 4
Doc 4 Site 5

2. If there’s an overlap for a doctor’s time at any other site (same doctor, same date, overlapping time of any sorts), the conflicting rows should get color coded to show a conflict.
Please note: Start and end times excluded from this criteria. Example: one site ends at 11 AM and another starts at 11 AM, this is not a conflict and should not highlight the rows.

From the attached example, Row 6 and 7 should get highlighted for conflict.



Please help!
Thanks in advance!