Adding record above the next line based on conditions. VBA required?

Hi,

Wondering if anybody can assist. I have attached the Excel file.

The original worksheet shows the original data arrangement, and the desired output is the final output.


The logic is....whenever the break down necessary column shows a y in the original worksheet, we need to add a row ABOVE.

As you can see, the first row in the desired output sheet remains the same. On the added line as you can see, the start_date becomes the end_date of the previous line, and its end_date is whenever the original end_date was. On the 3rd line, the start_date and end_date is the same in this case. All three lines will have the same admission_date assigned. We get 60 for the days_counted because it's just the subtraction of start_date from end_date.

I know it may seem redundant the way that record needs to be inserted, but it will satisfy our reporting standard.

Anyone able to assist? Can this be done in Excel or with some VBA? The actual worksheet would have many patient records, each identified by a unique patient ID. I would like to do this for each patient. So, it has to first check if two records belong to the same patient. (i.e. it has to be all 2, or all 3, or all 4).

Is this clear?

Thank you