Unique Values from 2 Columns of a table to an array

nikhilsharma · Jan 5, 2020

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.

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:

Array Column 1	Array Column 2
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!

J.Ty. · Jan 6, 2020

Hi,

Welcome to the Board!

I have made a first attempt. Please have a look, test and tell me if it resembles what you need.
You can play with in in Excel online here.

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

1

Doc1

Begin

End

Site

Unique

Doc1

Begin

End

Site

Problem1

Doc1

Begin

End

Site

Problem2

Doc

Site

2

Doc1

10:00

12:00

Site1

Doc1

Site1

Doc3

9:00

12:00

Site3

0

Doc3

9:00

12:00

Site3

0

Doc1

Site1

3

Doc2

11:00

12:01

Site2

Doc2

Site2

Doc1

10:00

12:00

Site1

0

Doc1

10:00

12:00

Site1

0

Doc2

Site2

4

Doc3

9:00

12:00

Site3

Doc3

Site3

Doc2

11:00

12:01

Site2

1

Doc2

11:00

12:01

Site2

0

Doc3

Site3

5

Doc1

12:00

17:00

Site2

Doc1

Site2

Doc1

12:00

17:00

Site2

0

Doc1

12:00

17:00

Site2

0

Doc1

Site2

6

Doc2

12:00

14:00

Site3

Doc2

Site3

Doc2

12:00

14:00

Site3

0

Doc2

12:00

14:00

Site3

1

Doc2

Site3

7

Doc3

17:00

17:15

Site1

Doc3

Site1

Doc3

12:00

17:00

Site2

0

Doc3

12:00

17:00

Site2

0

Doc3

Site1

8

Doc1

18:00

20:00

Site2

Doc3

Site2

Doc2

14:00

17:00

Site3

0

Doc2

14:00

17:00

Site3

0

Doc1

Site2

9

Doc2

14:00

17:00

Site3

Doc1

Site3

Doc3

17:00

17:15

Site1

0

Doc3

17:00

17:15

Site1

0

Doc2

Site3

10

Doc3

12:00

17:00

Site2

Doc1

18:00

20:00

Site2

0

Doc1

18:00

20:00

Site2

0

Doc3

Site2

11

Doc1

20:00

21:00

Site3

Doc1

20:00

21:00

Site3

0

Doc1

20:00

21:00

Site3

0

Doc1

Site3

Sheet1

Cell Formulas
Range	Formula
E2	E2	=UNIQUE(T2:U11)
H2	H2	=SORT(A2:D11,2)
N2	N2	=SORT(A2:D11,2)
T2	T2	=A2:A11
U2	U2	=D2:D11
L2:L11	L2	=COUNTIFS(H3:H11,H2,I3:I11,"<"&J2)
R2:R11	R2	=COUNTIFS(N$1:N1,N2,P$1:P1,">"&O2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A11	Expression	=COUNTIFS($H$2:$H$11;A2;$I$2:$I$11;B2;$L$2:$L$11;">0")+COUNTIFS($N$2:$N$11;A2;$O$2:$O$11;B2;$R$2:$R$11;">0")>0	text	NO

nikhilsharma · Jan 6, 2020

J.Ty. said:
Hi,

Welcome to the Board!

I have made a first attempt. Please have a look, test and tell me if it resembles what you need.
You can play with in in Excel online here.

Book1
A B C D E F G H I J K L M N O P Q R S T U
1 Doc1 Begin End Site Unique Doc1 Begin End Site Problem1 Doc1 Begin End Site Problem2 Doc Site
2 Doc1 10:00 12:00 Site1 Doc1 Site1 Doc3 9:00 12:00 Site3 0 Doc3 9:00 12:00 Site3 0 Doc1 Site1
3 Doc2 11:00 12:01 Site2 Doc2 Site2 Doc1 10:00 12:00 Site1 0 Doc1 10:00 12:00 Site1 0 Doc2 Site2
4 Doc3 9:00 12:00 Site3 Doc3 Site3 Doc2 11:00 12:01 Site2 1 Doc2 11:00 12:01 Site2 0 Doc3 Site3
5 Doc1 12:00 17:00 Site2 Doc1 Site2 Doc1 12:00 17:00 Site2 0 Doc1 12:00 17:00 Site2 0 Doc1 Site2
6 Doc2 12:00 14:00 Site3 Doc2 Site3 Doc2 12:00 14:00 Site3 0 Doc2 12:00 14:00 Site3 1 Doc2 Site3
7 Doc3 17:00 17:15 Site1 Doc3 Site1 Doc3 12:00 17:00 Site2 0 Doc3 12:00 17:00 Site2 0 Doc3 Site1
8 Doc1 18:00 20:00 Site2 Doc3 Site2 Doc2 14:00 17:00 Site3 0 Doc2 14:00 17:00 Site3 0 Doc1 Site2
9 Doc2 14:00 17:00 Site3 Doc1 Site3 Doc3 17:00 17:15 Site1 0 Doc3 17:00 17:15 Site1 0 Doc2 Site3
10 Doc3 12:00 17:00 Site2 Doc1 18:00 20:00 Site2 0 Doc1 18:00 20:00 Site2 0 Doc3 Site2
11 Doc1 20:00 21:00 Site3 Doc1 20:00 21:00 Site3 0 Doc1 20:00 21:00 Site3 0 Doc1 Site3
Sheet1
Cell Formulas
Range Formula
E2 E2 =UNIQUE(T2:U11)
H2 H2 =SORT(A2:D11,2)
N2 N2 =SORT(A2:D11,2)
T2 T2 =A2:A11
U2 U2 =D2:D11
L2:L11 L2 =COUNTIFS(H3:H11,H2,I3:I11,"<"&J2)
R2:R11 R2 =COUNTIFS(N$1:N1,N2,P$1:P1,">"&O2)
Cells with Conditional Formatting
Cell Condition Cell Format Stop If True
A2:A11 Expression =COUNTIFS($H$2:$H$11;A2;$I$2:$I$11;B2;$L$2:$L$11;">0")+COUNTIFS($N$2:$N$11;A2;$O$2:$O$11;B2;$R$2:$R$11;">0")>0 text NO

Thanks J.Ty. I am looking for a VBA approach though for the unique values part.
The intent is, once I have the doctor and site unique combinations in an array, I'd loop through it it filter the table and export individual PDFs.
Sorry for not being clear or more descriptive earlier. Appreciate your help.

Please advise.

Thanks,
N

Fluff · Jan 6, 2020

Cross posted on multiple sites.
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Please supply all relevant links

nikhilsharma · Jan 6, 2020

Fluff said:
Cross posted on multiple sites.
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Please supply all relevant links

Sorry, Seems like I messed up and have been getting a lot of flak about this on other forums too.
Here are the links to cross postings: