SOLVED

best practice for excel Data base

Super Contributor

Hello,

 

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

7 Replies

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

 

 

 

 

 

 

 

@Riny_van_Eekelen 

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? 

 

@Jihad Al-Jarady 

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.

 

 

@Riny_van_Eekelen 

 

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

 

 

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

Hi @Riny_van_Eekelen 

I appreciate your help. 

The data is collected in daily basis, but we use it for weeks.

 

Many thanks 

best response confirmed by Jihad Al-Jarady (Super Contributor)
Solution

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