Reverse Two Way Lookup With Duplicates

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,236
Office Version
  1. 365
Platform
  1. Windows
Dear Smartest Excelers Around,

I am looking for a formula solution to this problem.

I have an appointment schedule shaped like a table and I need to extract data from it into a sub table. The schedule has date column headers, time row headers, and the intersection of the date and time headers is a cell with a patient’s name. There are duplicate patient names in the schedule. The extract table should list the patient names vertically and next to each patient name should be a list of dates and times. The time and date for a single appointment should be contained in one cell.

For simplicity, I will not use dates and times in my example, but instead I will use letters.

Schedule looks like this:

Date Column headers are as follows: B1 = m, C1 = n, D1 = o, E1 = p
Time Row headers are as follows: A2 = q, A3 = r, A4 = s, A5 = t, A6 = u, A7 = v
The cells that contain patient names are: B2 = Jo, C3 = Sioux, C4 = Sioux, D2 = Sioux, E3 = Chin


The extract table looks like this:

Column headers are as follows: B20 = # App, C20 = App 1, D20 = App 2, E20 = App 3
Row headers (patient names) are as follows: A21 = Chin, A22 = Jo, A23 = Sioux
The cells that contain times and dates are: C21 = “r, p”, C22 = “q, m”, C23 = “r, n”, D23 = “s, n”, E23 = “q, o”, where the first letter is a time and the second letter is a date.



A while back I posted a question about this sort of reverse two way lookup:
http://www.mrexcel.com/forum/showthread.php?t=345657

Since then I have encountered the problem of the patients showing up in more than one time slot on a single day and also showing up in the schedule on more than one day. The formulas I am using are not working. So in my example, the trouble I am having comes from the fact that Sioux’s name shows up in more than one time slot for one day and also he shows up on more than one day.

If it is easier to see the table with dates and times, the data is here:

Schedule looks like this:
Date Column headers are as follows: B1 = 1/8/2010, C1 = 1/9/2010, D1 = 1/10/2010, E1 = 1/11/2010
Time Row headers are as follows: A2 = 8:00 AM, A3 = 9:00 AM, A4 = 10:00 AM, A5 = 11:00 AM, A6 = 12:00 PM, A7 = 1:00 PM
The cells that contain patient names are: B2 = Jo, C3 = Sioux, C4 = Sioux, D2 = Sioux, E3 = Chin

The extract table looks like this:
Column headers are as follows: B20 = # App, C20 = App 1, D20 = App 2, E20 = App 3
Row headers (patient names) are as follows: A21 = Chin, A22 = Jo, A23 = Sioux
The cells that contain patient names are: C21 = 9:00 AM, 1/11/10, C22 = 8:00 AM, 1/8/10, C23 = 9:00 AM, 1/9/10, D23 = 10:00 AM, 1/9/10, E23 = 8:00 AM, 1/10/10

Any help would be appreciated.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Do you mean something along the lines of the below ?

Excel Workbook
ABCDE
108-Jan09-Jan10-Jan11-Jan
208:00JoSioux
309:00SiouxChin
410:00Sioux
511:00
612:00
713:00
8
19
20# AppApp 1App 2App 3
21Chin111-Jan 09:00
22Jo108-Jan 08:00
23Sioux309-Jan 09:0009-Jan 10:0010-Jan 08:00
Sheet4
 
Upvote 0
Hi Mike,

I am curious to see the answer to this,
so hopefully this will bump it up for you, and someone more helpful can jump in.

By the way did you want ALL the appointments for ALL the Patience listed
Or did you want to be able to select the patient name?

I have got as far as this by selecting a name...

<TABLE style="WIDTH: 728pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=969><COLGROUP><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2413" width=66><COL style="WIDTH: 116pt; mso-width-source: userset; mso-width-alt: 5632" width=154><COL style="WIDTH: 116pt; mso-width-source: userset; mso-width-alt: 5668" width=155><COL style="WIDTH: 117pt; mso-width-source: userset; mso-width-alt: 5705" width=156><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1133" width=31><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 18pt; mso-width-source: userset; mso-width-alt: 877" width=24><COL style="WIDTH: 107pt; mso-width-source: userset; mso-width-alt: 5229" width=143><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 55pt; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl150 height=20 width=73></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 50pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_1740103 class=xl151 width=66>8/1/10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 116pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl151 width=154>9/1/10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 116pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl151 width=155>10/1/10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 117pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl151 width=156>11/1/10</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; WIDTH: 23pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #c0c0c0" class=xl152 width=31></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 64pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl167 width=85>sioux</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; WIDTH: 62pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #c0c0c0" class=xl153 width=82>G1 = Search</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; WIDTH: 18pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #c0c0c0" class=xl153 width=24></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; WIDTH: 107pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl154 width=143></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl155 height=20 align=right>8:00 AM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl142>Jo</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl142></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl142>Sioux</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl142></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl142 align=right>3</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl168>G2 = CountIF</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl145></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl156></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl155 height=20 align=right>9:00 AM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl142></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl142>Sioux</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl142></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl142>Chin</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl149>Time </TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl149>Date</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl145></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl157>Text Formatting</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl155 height=21 align=right>10:00 AM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl142></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl142>Sioux</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl142></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl142></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl148>8:00 AM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl146>10/1/10</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl145></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl158>8:00 AM, 10/01/10</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl155 height=21 align=right>11:00 AM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl142></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl142></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl142></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl142></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl148>9:00 AM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl146>9/1/10</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl145></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl158>9:00 AM, 09/01/10</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl155 height=21 align=right>12:00 PM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl142></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl142></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl142></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl142></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl148>10:00 AM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl146>9/1/10</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl145></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl158>10:00 AM, 09/01/10</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl155 height=21 align=right>1:00 PM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl142></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl142></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl142></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl142></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl159></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl147></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl156></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl160 height=20></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl156></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl160 height=20></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl156></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl161 height=20></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl143># App</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl143>App 1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl143>App 2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl143>App 3</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl145></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl145></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl145></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl156></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl162 height=20>Chin</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl142></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl144></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl144></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl144></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl156></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl162 height=20>Jo</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl142></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl144></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl144></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl144></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl156></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl163 height=21>Sioux</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl164></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl169>8:00 AM, 10/01/10</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl169>9:00 AM, 09/01/10</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl169>10:00 AM, 09/01/10</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl165></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl165></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl165></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl165></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl166></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" height=21></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl170>*=IF($G$1=$A13,$J$4,"")</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl171>*=IF($G$1=$A13,$J$5,"")</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl172>*=IF($G$1=$A13,$J$6,"")</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0"></TD></TR></TBODY></TABLE>


For G4:G6 and H4:H6 I am using the formulas provided by Aladin and Dominic...
http://www.mrexcel.com/forum/showthread.php?t=345657


I am using this is J4 and copied down...
=TEXT($G4,"h:mm AM/PM,")&TEXT($H4," mm/dd/yy")

Personally, I would like to see a completed table rather than one based on a name selection.

I look forward to the www.youtube.com/user/ExcelIsFun video on this solution.

Ak

Love the videos by the way, they (you) have taught me a lot,
but obviously not enough to help you out :(
 
Upvote 0
Mike, I missed the point re: formatting...

The values returned in the Appointment table are datetime values given the results equate to Date (integer) + Time (decimal)
As such you can apply whatever Custom Format you desire to the Range, eg: h:mm AM/PM, m/d/yy
 
Upvote 0
Hi DonkeyOte,

Thats great for me.
If you don't mind, could you explain your formula please?

Thanks

Ak
 
Upvote 0
Presumably you're referring to the Array formula ?

It simply populates the SMALL with an array of values, 1 per cell of the Schedule range (in the case of the example that's 24 values - B2:E7)

If the name in the top table matches the name in Col A it will populate the Array with the Sum of the date header (Integer) and the Time header (decimal)

If the value <> name it will simply default and populate with Boolean FALSE.

Using example of C21:

SMALL({FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,40189.375;FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE},COLUMNS($C21:C21)

COLUMNS function is used to determine k such that it increments as the formula is copied across - ie Column C will retrieve the smallest numerical value from the above array (k = 1) whereas Col D will retrieve the 2nd smallest value from the above array (k = 2)

If you wish to work through a formula it's often a good idea to make use of the Evaluate Formula tool - or highlight a particular section of a formula in the formula bar and press F9 to see how it evaluates.
 
Upvote 0
DonkeyOte!!!!

What a great insight - the dates and times are numbers!! I was trying to figure a way to deal with the ties spit out by the MATCH function that came from Sioux's name appearing on the same day - and then your solution of adding the dates and times gives unique and sequentially larger number for each date and time combo!

Just awesome, DonkeyOte!

Thanks for the solution.
 
Upvote 0
DonkeyOte's table of data from above is exactly the setup that I have in my table (his is a better visual description than my written description). Can anyone think of a formula solution that would work if the column and row headers were not dates and times? If the column and row headers were words, for example.
 
Upvote 0
There are different approaches to this kind of thing... dealing with text is invariably more cumbersome than numerics but below would be one (expensive) approach:

Excel Workbook
ABCDE
1mnop
2qJoSioux
3rSiouxChin
4sSioux
5t
6u
7v
8
18
19
20# AppApp 1App 2App 3
21Chin1p,r
22Jo1m,q
23Sioux3o,qn,rn,s
Sheet1
 
Upvote 0
Another way would be to create a lookup table somewhere to give you numbers to "stand-in" for the letters. You can INDEX against those "stand-in" numbers instead of against the text.
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top