Help setting up some formulas for a spreadsheet of daily data reports...

Good Evening!

I have a project where the main goal is to summarize / analyze certain characteristics of medication use within in a healthcare facility.

For data, I have been given a number of individual daily medication use reports in Excel file format (e.g., 30 separate excel files for each day of a given month).

So far, I have batch-combined these into one spreadsheet...I have uploaded a simplified mock version to represent what the dataset currently looks like (The "real" report has 15 columns of data).


Now, I would like to determine the following from this database (in the most efficient way possible!):

1-Determine the # of days of CONSECUTIVE use of the medication for each individual patient in the database (the “duration of treatment”). *Note that there will be many, many patients in the real database (100+)...each with a unique numerical identifier

2-Determine the total number of patients who received the medication based on certain qualifiers (e.g., "in a certain location of the facility", "during a certain given week, month, etc."). *Note that there will be no more than 8 unique location options)

3-Provide summary characteristics for the medication use in the facility, such as:
--mean/median duration of treatment, etc.
--the # of patients who received more than “x” consecutive days of therapy
--the # of patients who received a certain dose (note that there will be a limited number (<5) of possible doses)


I know from doing some quick searches of the forum so far that these types of things CAN be done fairly efficiently on a dataset like what I’m getting through use of some of the more complicated Excel formula functions...BUT many of these I’m not yet 100% comfortable developing/using yet...thus my post!

So, I’d welcome any and all advice on how to sort my data, transform it [if needed], set up some formulas, etc to do my task without wasting huge amounts of time...

Thanks in advance for all help!