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!

Originally Posted by nikhilsharma30

Sorry Marc for the oversight. I edited the post to mention the cross posting. Apologies.

Along with the mention we require a link to the post. Please see the red link in Marc's post to understand why.

You also have a cross-post at MrExcel.

Please include links to both. As a new member you will not be able to post hot links but you can post the URLs. After editing your post (but before submitting it) click Go Advanced under your post and uncheck "Automatically parse links in text".

No further posts to this thread until links have been provided. Thanks.