Reverse Two Way Lookup With Duplicates

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: Reverse Two Way Lookup With Duplicates

  1. #1
    Board Regular
    Join Date
    Dec 2005
    Location
    Seattle, WA
    Posts
    1,176
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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 patients 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 Siouxs 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.
    Sincerely, Mike Girvin

  2. #2
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Reverse Two Way Lookup With Duplicates

    Do you mean something along the lines of the below ?

    Sheet4

     ABCDE
    1 08-Jan09-Jan10-Jan11-Jan
    208:00Jo Sioux 
    309:00 Sioux Chin
    410:00 Sioux  
    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

    Spreadsheet Formulas
    CellFormula
    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
    Does my a$$ look big in this picture ?

  3. #3
    Board Regular Akashwani's Avatar
    Join Date
    Mar 2009
    Posts
    2,900
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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/109/1/1010/1/1011/1/10siouxG1 = Search
    8:00 AMJoSioux3G2 = CountIF
    9:00 AMSiouxChinTime DateText Formatting
    10:00 AMSioux8:00 AM10/1/108:00 AM, 10/01/10
    11:00 AM9:00 AM9/1/109:00 AM, 09/01/10
    12:00 PM10:00 AM9/1/1010:00 AM, 09/01/10
    1:00 PM
    # AppApp 1App 2App 3
    Chin
    Jo
    Sioux8:00 AM, 10/01/109:00 AM, 09/01/1010: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
    A reply is not only helpful to others, but polite to those who have provided a solution!!

    To post sample data go here....
    HtmlMaker
    Excel Genie
    Use Borders

    Upload a Sample File


    Visit here for some EXCELlent videos...

  4. #4
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Does my a$$ look big in this picture ?

  5. #5
    Board Regular Akashwani's Avatar
    Join Date
    Mar 2009
    Posts
    2,900
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    A reply is not only helpful to others, but polite to those who have provided a solution!!

    To post sample data go here....
    HtmlMaker
    Excel Genie
    Use Borders

    Upload a Sample File


    Visit here for some EXCELlent videos...

  6. #6
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    Does my a$$ look big in this picture ?

  7. #7
    Board Regular
    Join Date
    Dec 2005
    Location
    Seattle, WA
    Posts
    1,176
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    Sincerely, Mike Girvin

  8. #8
    Board Regular
    Join Date
    Dec 2005
    Location
    Seattle, WA
    Posts
    1,176
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    Sincerely, Mike Girvin

  9. #9
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

     ABCDE
    1 mnop
    2qJo Sioux
    3r Sioux Chin
    4s Sioux
    5t
    6u
    7v
    8
    18     
    19     
    20 # AppApp 1App 2App 3
    21Chin1p,r  
    22Jo1m,q  
    23Sioux3o,qn,rn,s

    Spreadsheet Formulas
    CellFormula
    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
    Does my a$$ look big in this picture ?

  10. #10
    Board Regular gardnertoo's Avatar
    Join Date
    Jul 2007
    Location
    Goldendale, WA
    Posts
    936
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    Gardnertoo: I just like to smile! Smiling's my favorite.
    Boss: Make work your favorite. That's your new favorite.

    Home: Windows 7, Excel 2013
    Previous Employer: Windows XP and Windows 7, Excel 2003 and 2007 and 2010
    Current Employer: Google Sheets

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

DMCA.com