+ Reply to Thread
Results 1 to 11 of 11

Listing Multiple Instances per Person in Pivot

  1. #1
    Registered User
    Join Date
    03-17-2020
    Location
    Yakima, WA
    MS-Off Ver
    Microsoft Office 365 Pro Plus
    Posts
    30

    Listing Multiple Instances per Person in Pivot

    I have been trying to figure out a way to take a list of hospital discharges and a list of clinic follow-up appointments, and calculate if the appointment happened within 3 days or 7 days. I abandoned my first attempts of using formulas to combine the spreadsheets. Now I am trying to group the data in a pivot table, which I hope will allow me to calculate the days between hospital admissions and follow-up visits.

    I can make a pivot table, but I cannot figure out how to get the date of the visit underneath the Visit Type. Is there a way to do this?

    Or is there a better way to solve this problem?
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    01-14-2013
    Location
    Austria
    MS-Off Ver
    2016 / 2019
    Posts
    339

    Re: Listing Multiple Instances per Person in Pivot

    I do not know if it is possible with functions and pivot table.
    It should be possible with Power Query and also with VBA

    some questions on your tables.
    1) Angel369 has a followup before Emergency. Wrong date or another emergency/inpatient before and not in the table?
    2) Suzie557 has a followup on the discharge date. Correct? or should the next one the correct one.
    Are more followups after emergency/inpatient interesting?

    Could this be the desired result you want to see (may be after correction)?
    Attachment 677372

  3. #3
    Registered User
    Join Date
    03-17-2020
    Location
    Yakima, WA
    MS-Off Ver
    Microsoft Office 365 Pro Plus
    Posts
    30

    Re: Listing Multiple Instances per Person in Pivot

    I can't see the attachment. When I click the link it says invalid attachment.

  4. #4
    Valued Forum Contributor
    Join Date
    01-14-2013
    Location
    Austria
    MS-Off Ver
    2016 / 2019
    Posts
    339

    Re: Listing Multiple Instances per Person in Pivot

    strange. when I click it opens !?

    excel instead of the picture. open questions are belonging to the cells with red border (sheet "Combined")
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-17-2020
    Location
    Yakima, WA
    MS-Off Ver
    Microsoft Office 365 Pro Plus
    Posts
    30

    Re: Listing Multiple Instances per Person in Pivot

    A patient could have a visit before going to the ED... it's not really considered a follow-up visit. It may be unrelated, or it may be when the MD told them to go to the ED. We don't really need to know about those. We are just counting if the patient was seen *after* a hospital visit. We are assuming that it they are being seen in follow-up for the preceding hospitalization. My limitations are with the lists that I am given (a list of all hospital visits, and a list of all clinic visits). If there are multiple clinic visits after a hospital visit, we don't really need to know about those, we just want to know if one of them was within the day parameters.

  6. #6
    Registered User
    Join Date
    03-17-2020
    Location
    Yakima, WA
    MS-Off Ver
    Microsoft Office 365 Pro Plus
    Posts
    30

    Re: Listing Multiple Instances per Person in Pivot

    Another layer of complexity is that we need to break it down by hospital. I didn't specify that before or put it in my example, but eventually I need to do that, too. I presume, so we could see if we were more successful with following up depending on which hospital the patient discharged from (they have different methods and lag times in notifying us).

  7. #7
    Valued Forum Contributor
    Join Date
    01-14-2013
    Location
    Austria
    MS-Off Ver
    2016 / 2019
    Posts
    339

    Re: Listing Multiple Instances per Person in Pivot

    different lag times do not really add complexity. Only provide another table with lag times that can be merged with the final result.

    How is it with my question about
    Suzie557 has a followup on the discharge date. Correct? or should the next one the correct one.

    How do you want to visualize the "in or out lag time"? extra column with true/false, conditional formatting or simple calculation of days?

  8. #8
    Valued Forum Contributor
    Join Date
    01-14-2013
    Location
    Austria
    MS-Off Ver
    2016 / 2019
    Posts
    339

    Re: Listing Multiple Instances per Person in Pivot

    I prepared a solution with Power Query which is part of your Office 365 (Ribbon "Data" - "Get & Transform")

    one table can be used as data source for a pivot table,
    the other one is almost final (I think) - any calculation of lag times can be done with Conditional formatting (may be also including a table of lag times for different hospitals)

    cheers
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    03-17-2020
    Location
    Yakima, WA
    MS-Off Ver
    Microsoft Office 365 Pro Plus
    Posts
    30

    Re: Listing Multiple Instances per Person in Pivot

    In answer to your question, it is probably unlikely that it would ever happen this way, that a follow-up would happen on the same day. I think I would exclude 3/27 and take the next date of 4/4.

  10. #10
    Registered User
    Join Date
    03-17-2020
    Location
    Yakima, WA
    MS-Off Ver
    Microsoft Office 365 Pro Plus
    Posts
    30

    Re: Listing Multiple Instances per Person in Pivot

    Thank you for this. It looks like it might be my solution. I have never used Power Query. If I am to reproduce what you created with the real data, I'm not sure of the next step. I see your Queries in the list. Maybe I will have to play with it. Can you point me in the direction of a basic tutorial for Power Query?

  11. #11
    Valued Forum Contributor
    Join Date
    01-14-2013
    Location
    Austria
    MS-Off Ver
    2016 / 2019
    Posts
    339

    Smile Re: Listing Multiple Instances per Person in Pivot

    I do not know how you get /handle new data. The easiest way is to copy new data into both available tables.
    Power Query uses always the table names. So if you change the names then you have to adopt the query --> Therefore it is easier to rename the table.

    If you want to use Power Query in your original file then
    if possible rename your original tables to "tblHospital" and "tblFollowUp" (Power Query is case sensitive!)
    If not then you have to adopt both connections (tblHospital, tblFollowUp). Open them by double click, select the Applied Step "Source" and change the table name.
    - select all Queries and copy it (CTRL C)
    - open the "Queries & Connections" pane - should be empty if not used before
    - select the pane with the mouse and paste (CTRL V)

    This will immediately start the process and will generate two new sheets with both new result tables.
    If you only want to use one then deselect one of the "Generate .." queries before copying or delete the sheet and the query afterwards.

    How it works:
    - I imported both tables as links. This prevents a "firewall error". You may face such a problem if you work more with Power Query.
    - everytime when you update a table you have to "Refresh all" (on ribbon "Data") or refresh the query itself (when it is visible - right mouse click on query and "refresh")

    There are many books available. I started with
    "M is for (DATA) MONKEY" from Ken Puls and Miguel Escobar and
    "Collect, Combine and Transform Data Using Power Query in Excel and Power BI" from Gil Raviv

    There are a lot of free forums like
    https://www.myonlinetraininghub.com/excel-forum
    https://www.excelguru.ca/forums/foru...rm)-amp-M-code
    https://powerpivotforum.com.au/viewforum.php?f=5

    or samples like
    https://www.myonlinetraininghub.com/...ry/power-query
    https://bondarenkoivan.wordpress.com/
    and many more

    Hope this helps

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Listing numbers in one cell by person's name
    By JasonNeedsHelp in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-29-2018, 04:02 PM
  2. [SOLVED] Listing numbers in one cell by person's name
    By JasonNeedsHelp in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-29-2018, 02:24 PM
  3. Listing all row instances associated with one column value
    By C_Willy in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-17-2013, 03:27 AM
  4. Transpose/Pivot multiple rows per person into 1 row per person with fixed columns
    By MaestroEnrique in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-13-2013, 06:35 AM
  5. [SOLVED] Count of instances of offenses per person
    By bpiroma in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-07-2012, 05:16 PM
  6. [SOLVED] pivot table with multiple instances
    By J in forum Excel General
    Replies: 3
    Last Post: 02-21-2006, 12:10 PM
  7. [SOLVED] Why are there multiple instances of same person on shared workshee
    By mcodyw in forum Excel General
    Replies: 0
    Last Post: 03-31-2005, 02:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1