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

Hello there,

Attached is your original workbook updated to include a macro that provides you with the desired results you have listed on the "desired output" worksheet.

To run it select the "original" worksheet and then hold down the alt key and press F8 to open the macro window. Select "Macro1" and then click Run.

To see the code hold down the Alt key and press F8 and select the Macro1. The select the Step Into option. I have inserted comments to help you understand.

Also I have inserted a formula into column F to assign y's and n's.

Let me know if this works for you!

Thanks!

The code worked!

Thank you for using the comment lines(it makes understanding the code easier!)

I see that the use of the .Offset() is useful in this situation. Is the following possible?

I would like the days_counted be a calculation.(it's just the end_date minus the start_start)(the 60 was manually calculated by using a formula in the formula bar).

Also, is it possible run this macro and have Excel output the dataset in a different worksheet? It's just that I don't want to lose the original.

Thanks

That's totally possible, right now I'm working on something else for someone but once I'm finished I can do this for you. Won't take too long if someone hasn't helped you by then

Okay this one works!

Thanks!

Hi,

Appreciate your help.

I am putting this one on hold for the moment, but will take a look at it once I make a breakthrough on the other request.

No problem, I'm going to unsubscribe to this thread for now then, but if you need additonal help with this you can private message me with a link to this post and I will continue to help!

Thanks!