Aug 21 2022 11:03 PM
Aug 21 2022 11:03 PM
I have the workbook that contains information about 20 health Centers and mobile teams that we collect the data from them in weekly basis.
I would like to know the best practice to accumulate the data in the workbook and the manipulate and use this data to generate reports and charts for decision makers.
many thanks in advance
Aug 22 2022 12:06 AM
@Jihad Al-Jarady Not sure if it conforms with best practice, but if I would have to set this up from scratch, I would collect data in a tabular format, and have your health centers record every single visit/consultation with their relevant attributes. Create a table with columns for e.g. date, time, center, gender, age, diagnosis, new/follow-up, hospital referral Y/N. Join them all together with e.g. Power Query. You may end up with thousands of rows of data over the year, but it will not be much of a problem for Excel to work out the week numbers, the age groups and summarize it all by week, by diagnosis, by center, by district, by age or whatever way you want.
The workbook you have now has what I would call some classic design faults that makes analyzing/summarizing/filtering near to impossible. To name few:
Merged cells, multi-row headers, far to many columns (who could ever grasp/oversee 440 columns?), columns with sub-totals, hidden columns.
Aug 22 2022 12:47 AM
I appreciate your quick response.
So, If will create it from the scratch, it would be better to separate the information into tables then connect them together with a relationship?
I create a table in worksheet Consultation, then I entered the record 4 times to differentiate it,( 2 records for Male and 2 for Female, the for each one for new and one for follow up) see attachment
What is the best way to deal with table that has merge cells?
Aug 22 2022 02:43 AM
What do you mean by "connect them together with a relationship" ?
But if I would take your "Consultation" sheet, I'd connect to it with Power Query (PQ), unpivot the data and create a table as shown in Table1 and then a pivot table from that one.
The attached file contains a very crude example.
With regard to dealing with tables that have merged cells, you can handle it PQ, but that would be extra work. Better to avoid them all together.
Aug 22 2022 10:58 PM
Forget about this "connect them together with a relationship" ?
What is the difference between the data arrange is consultation tab and the table you created?
If I am going to use the table you created, It means I need to enter 20 records for each day, while in the consultation table it will be 4 records only ( I am using the age groups as rows).
I really appreciate your support
Aug 23 2022 02:37 AM
@Jihad Al-Jarady The Consultation table is totally acceptable and it enables you to connect to it with PQ and easily create the kind of table I created so that you can build a pivot table on that one. You don't need to collect data as in the green table.
I was just wondering how you came up with this aggregated data. Is someone counting visits for e.g. males in a particular age group, this week, with a particular diagnosis to return the number 14? If so, how does that raw data look like? If it is a sequential list of visits in Excel with relevant patient info and diagnosis, you could connect to those lists directly, without the need to create the Consultation table.
In other words, if a medical facility produces a list with say 1000 records, one for each visit, you could use PQ to summarize it for you and determine that there were 14 boys under 5 years old with lower respiratory infections among those 1000 visits. And those lists/tables may contain thousands of rows and you can have many of them. As long as they are consistent (i.e. the same structure for every medical center) it's going to be relatively easy for PQ to prepare the basis for further analysis.
Aug 24 2022 04:12 AMSolution
@Jihad Al-Jarady Well, then you could consider to stop doing the intermediate summary by week and work off a table that may contain max 140 rows (20 centers X 7 days) per week, as long as every row has a date to that you can "calculate" the weekly numbers.