Thanks:  0
Likes:  0

# Thread: Reverse Two Way Lookup With Duplicates

1. ## Reverse Two Way Lookup With Duplicates

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.

2. ## Re: Reverse Two Way Lookup With Duplicates

Do you mean something along the lines of the below ?

Sheet4

 A B C D E 1 08-Jan 09-Jan 10-Jan 11-Jan 2 08:00 Jo Sioux 3 09:00 Sioux Chin 4 10:00 Sioux 5 11:00 6 12:00 7 13:00 8 19 20 # App App 1 App 2 App 3 21 Chin 1 11-Jan 09:00 22 Jo 1 08-Jan 08:00 23 Sioux 3 09-Jan 09:00 09-Jan 10:00 10-Jan 08:00

Spreadsheet Formulas
 Cell Formula B21 =COUNTIF(\$B\$2:\$E\$7,\$A21) C21 {=IF(COLUMNS(\$C21:C21)>\$B21,"",SMALL(IF(\$B\$2:\$E\$7=\$A21,\$B\$1:\$E\$1+\$A\$2:\$A\$7),COLUMNS(\$C21:C21)))}
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

Excel tables to the web >> Excel Jeanie HTML 4

3. ## Re: Reverse Two Way Lookup With Duplicates

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...

 8/1/10 9/1/10 10/1/10 11/1/10 sioux G1 = Search 8:00 AM Jo Sioux 3 G2 = CountIF 9:00 AM Sioux Chin Time Date Text Formatting 10:00 AM Sioux 8:00 AM 10/1/10 8:00 AM, 10/01/10 11:00 AM 9:00 AM 9/1/10 9:00 AM, 09/01/10 12:00 PM 10:00 AM 9/1/10 10:00 AM, 09/01/10 1:00 PM # App App 1 App 2 App 3 Chin Jo Sioux 8:00 AM, 10/01/10 9:00 AM, 09/01/10 10:00 AM, 09/01/10 *=IF(\$G\$1=\$A13,\$J\$4,"") *=IF(\$G\$1=\$A13,\$J\$5,"") *=IF(\$G\$1=\$A13,\$J\$6,"")

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

4. ## Re: Reverse Two Way Lookup With Duplicates

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

5. ## Re: Reverse Two Way Lookup With Duplicates

Hi DonkeyOte,

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

Thanks

Ak

6. ## Re: Reverse Two Way Lookup With Duplicates

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.

7. ## Re: Reverse Two Way Lookup With Duplicates

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.

8. ## Re: Reverse Two Way Lookup With Duplicates

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.

9. ## Re: Reverse Two Way Lookup With Duplicates

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:

Sheet1

 A B C D E 1 m n o p 2 q Jo Sioux 3 r Sioux Chin 4 s Sioux 5 t 6 u 7 v 8 18 19 20 # App App 1 App 2 App 3 21 Chin 1 p,r 22 Jo 1 m,q 23 Sioux 3 o,q n,r n,s

Spreadsheet Formulas
 Cell Formula C21 {=IF(COLUMNS(\$C21:C21)>\$B21,"",INDEX(\$A\$1:\$E\$1,ROUND(100*MOD(SMALL(IF(\$B\$2:\$E\$7=\$A21,ROW(\$A\$2:\$A\$7)+(COLUMN(\$B\$1:\$E\$1)/100)),COLUMNS(\$C21:C21)),1),0))&","&INDEX(\$A\$1:\$A\$7,SMALL(IF(\$B\$2:\$E\$7=\$A21,ROW(\$A\$2:\$A\$7)),COLUMNS(\$C21:C21))))}
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

Excel tables to the web >> Excel Jeanie HTML 4

10. ## Re: Reverse Two Way Lookup With Duplicates

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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•