Unique Values from 2 Columns of a table to an array

nikhilsharma

New Member
Joined
Jan 5, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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 Image.png


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 1Array Column 2
Doc 1Site 1
Doc 1Site 2
Doc 2Site 1
Doc 2Site 2
Doc 2Site 3
Doc 2Site 4
Doc 3Site 1
Doc 3Site 2
Doc 3Site 4
Doc 4Site 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!
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

J.Ty.

Well-known Member
Joined
Feb 4, 2012
Messages
1,109
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
  2. Web
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
ABCDEFGHIJKLMNOPQRSTU
1Doc1BeginEndSiteUniqueDoc1BeginEndSiteProblem1Doc1BeginEndSiteProblem2DocSite
2Doc110:0012:00Site1Doc1Site1Doc39:0012:00Site30Doc39:0012:00Site30Doc1Site1
3Doc211:0012:01Site2Doc2Site2Doc110:0012:00Site10Doc110:0012:00Site10Doc2Site2
4Doc39:0012:00Site3Doc3Site3Doc211:0012:01Site21Doc211:0012:01Site20Doc3Site3
5Doc112:0017:00Site2Doc1Site2Doc112:0017:00Site20Doc112:0017:00Site20Doc1Site2
6Doc212:0014:00Site3Doc2Site3Doc212:0014:00Site30Doc212:0014:00Site31Doc2Site3
7Doc317:0017:15Site1Doc3Site1Doc312:0017:00Site20Doc312:0017:00Site20Doc3Site1
8Doc118:0020:00Site2Doc3Site2Doc214:0017:00Site30Doc214:0017:00Site30Doc1Site2
9Doc214:0017:00Site3Doc1Site3Doc317:0017:15Site10Doc317:0017:15Site10Doc2Site3
10Doc312:0017:00Site2Doc118:0020:00Site20Doc118:0020:00Site20Doc3Site2
11Doc120:0021:00Site3Doc120:0021:00Site30Doc120:0021:00Site30Doc1Site3
Sheet1
Cell Formulas
RangeFormula
E2E2=UNIQUE(T2:U11)
H2H2=SORT(A2:D11,2)
N2N2=SORT(A2:D11,2)
T2T2=A2:A11
U2U2=D2:D11
L2:L11L2=COUNTIFS(H3:H11,H2,I3:I11,"<"&J2)
R2:R11R2=COUNTIFS(N$1:N1,N2,P$1:P1,">"&O2)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A11Expression=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")>0textNO
 

nikhilsharma

New Member
Joined
Jan 5, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTU
1Doc1BeginEndSiteUniqueDoc1BeginEndSiteProblem1Doc1BeginEndSiteProblem2DocSite
2Doc110:0012:00Site1Doc1Site1Doc39:0012:00Site30Doc39:0012:00Site30Doc1Site1
3Doc211:0012:01Site2Doc2Site2Doc110:0012:00Site10Doc110:0012:00Site10Doc2Site2
4Doc39:0012:00Site3Doc3Site3Doc211:0012:01Site21Doc211:0012:01Site20Doc3Site3
5Doc112:0017:00Site2Doc1Site2Doc112:0017:00Site20Doc112:0017:00Site20Doc1Site2
6Doc212:0014:00Site3Doc2Site3Doc212:0014:00Site30Doc212:0014:00Site31Doc2Site3
7Doc317:0017:15Site1Doc3Site1Doc312:0017:00Site20Doc312:0017:00Site20Doc3Site1
8Doc118:0020:00Site2Doc3Site2Doc214:0017:00Site30Doc214:0017:00Site30Doc1Site2
9Doc214:0017:00Site3Doc1Site3Doc317:0017:15Site10Doc317:0017:15Site10Doc2Site3
10Doc312:0017:00Site2Doc118:0020:00Site20Doc118:0020:00Site20Doc3Site2
11Doc120:0021:00Site3Doc120:0021:00Site30Doc120:0021:00Site30Doc1Site3
Sheet1
Cell Formulas
RangeFormula
E2E2=UNIQUE(T2:U11)
H2H2=SORT(A2:D11,2)
N2N2=SORT(A2:D11,2)
T2T2=A2:A11
U2U2=D2:D11
L2:L11L2=COUNTIFS(H3:H11,H2,I3:I11,"<"&J2)
R2:R11R2=COUNTIFS(N$1:N1,N2,P$1:P1,">"&O2)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A11Expression=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")>0textNO

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,216
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 5, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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:

 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,216
Office Version
  1. 365
Platform
  1. Windows
Thanks for that.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,193
Messages
5,546,479
Members
410,742
Latest member
WalterSil
Top