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?

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

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

strange. when I click it opens !?

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

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.

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

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?

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

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.

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?

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