Forum Discussion
Need Help with Excel (how to best present info)
Dear all,
Hoping for a collective wisdom how to best present information that is coming from multiple excel files.
Have
- Spreadsheets coming from multiple sources
- Rows have patient names
- columns have various visits and data contains dates when these various visits are either occurring or projected
Need
- track what visits have occurred or will occur during each given date (i.e. sort by week/month) for each given patient
Not sure how to do this from multiple sources (i.e. one worksheet tracks treatment, the other worksheet tracks treatment visits, etc).
The specifics are as follows:
I need to combine information to be able to present the following:
For any given week, here is the information per patient what is needed or what was done from all 3 tables noted below:
Week date x to date y
Patient x has the following items done or coming up due
I get information from 3 different sources as follows (dates could be either projected or actual):
Table 1: from Imaging
| Patient Names | Imaging Type (i.e. MRI, CT, etc) | Visit Type (i.e. Visit X) | Visit Date Projected | Visit Date Actual |
| [Name] | CT or MRI, et | Visit X | Date (dd-MON-YYYY) | Date (dd-MON-YYYY) |
| [Name] | CT or MRI, et | Visit Y | Date (dd-MON-YYYY) | Date (dd-MON-YYYY) |
Table 2: from Phone Service (to collect phone calls, if any)
| Patient Names | Visit Type (i.e. Visit 1) | Visit Type (i.e. Visit 2) | Visit Type (i.e. Visit 3) |
| [Name] | Date (dd-MON-YYYY) | Date (dd-MON-YYYY) | Date (dd-MON-YYYY) |
| [Name] | Date (dd-MON-YYYY) | Date (dd-MON-YYYY) | Date (dd-MON-YYYY) |
Table 3: from Clinic
| Patient Names | Visit Type (i.e. Visit 1) | Visit Type (i.e. Visit 2) | Visit Type (i.e. Visit 3) |
| [Name] | Date (dd-MON-YYYY) | Date (dd-MON-YYYY) | Date (dd-MON-YYYY) |
| [Name] | Date (dd-MON-YYYY) | Date (dd-MON-YYYY) | Date (dd-MON-YYYY) |
If you could think of any other way to logically and easier to present or combine this info, I would be very grateful.
Thank you so much,
Sophie
4 Replies
- Hello,
If all the spreadsheets have the same structure such as same number of columns and exact headings, you can easily use Power Query to append the data and load back to Excel.
Thereafter, use PivotTable to achieve what you want your report to look likeAbiola1I wish it was that easy. Unfortunately, the structure is not the same. Not the same number of rows and columns. I was thinking of maybe revising one of the tables that has column headings as types of visits/treatments, but not sure how to do it. That way I can sort per patient per date listing all visit types/treatments within specified week/month. Thanks again for your help
It's better to discuss with the sample, the task is a bit abstract. In general to combine with Power Query it's not necessary to have same number of columns. However, some logic shall be defined how to map tables and what to combine with what, e.g. based on columns headers, but not necessary.
